# Create pivot table with same datafield in rows and columns

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?

|

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.

---
Kind regards, HansV
https://www.eileenslounge.com

Sorry this didn't help.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

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

Regards,

Ashish Mathur
www.ashishmathur.com

Sorry this didn't help.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

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

Sorry this didn't help.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thank you. I will try this tip too.

Sorry this didn't help.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?