Create pivot table with same datafield in rows and columns

Question

Hello,

i want to count how many times same person traveled with another person based on route id. Example of data:

PERSON	ROUTE ID
1	1
2	1
5	1
1	2
2	2
6	2
1	3
3	3
4	3
5	4
6	4
6	6

And i expect output like this, where column and rows are persons and data are counts traveled with other person:

PERSON	1	2	3	4	5	6
1	3	2	1	0	1	0
2	2	2	0	0	1	1
3	1	1	1	1	0	0
4	0	0	1	1	0	0
5	1	1	0	0	2	1
6	0	0	0	0	1	3

I thought with simple pivot table i could achieve this by adding same field in row label and column label and count of ROUTE ID to values but excel wont let me do that. Is there some workaround?

Answer 1

Thank you. I will try this tip too.

Answer 2

Thank you, i will try this solution. In company im working, we using a lot VBA macros so, this will fit in enviroment :)

P.S. Yeah i did made some mistake while manualy calculating second table. Sorry

Answer 3

Hi,

One can solve this with the PowerPivot.  Here are the PowerPivot formulas

Distinct Routes=

DISTINCTCOUNT(travel_data[Route ID])

Routes travelled together=

CALCULATE ([Distinct routes],CALCULATETABLE(SUMMARIZE(travel_data,travel_data[Route ID]),ALL(Persons),USERELATIONSHIP(travel_data[Person],Persons1[Person])))

You may read up further on my solution to a similar problem here.

Here's a screenshot

Answer 4

You could use a custom VBA function for this:

Function CountEm(data As Range, p1, p2) As Long

    Dim c1 As Range

    Dim c2 As Range

    Dim v As Variant

    For Each c1 In data.Columns(1).Cells

        If c1.Value = p1 Then

            v = c1.Offset(, 1).Value

            For Each c2 In data.Columns(1).Cells

                If c2.Value = p2 And c2.Offset(, 1).Value = v Then

                    CountEm = CountEm + 1

                End If

            Next c2

        End If

    Next c1

End Function

Let's say your data table is A1:B13, and the second table is D1:J7.

In E2 (the first cell in the "interior" of the second table), enter the formula

=CountEm($A$2:$B$13,$D2,E$1)

Fill down to row 7, then fill right to column J (or vice versa).

I get slightly different results than your sample "pivot table", by the way.