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

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

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

How satisfied are you with this reply?

Thanks for your feedback.

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
http://twitter.com/excelashish

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

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

How satisfied are you with this reply?

Thanks for your feedback.

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

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

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

How satisfied are you with this reply?

Thanks for your feedback.

Thank you. I will try this tip too.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

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

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info


Last updated September 14, 2020 Views 638 Applies to: