Calculating Popular Combinations

[harryp]

Hi guys,

I am new to the forum and looking for some help calculating popular routes of itineraries.

I want to determine which routes in a travel itinerary are most commonly put together.

There are multiple ItineraryID and each one will have multiple Locations. For example, the below data would display the table below it.

Itinerary Code / Location
101 /Thailand
101 /Cambodia
101 /Vietnam
102 /Vietnam
103 /Tanzania
103 /Cambodia
104 /Cambodia
104 /Thailand
104 /Vietnam
105 /Thailand
105 /Vietnam
106 /Vietnam


To display:

Combination Count

Vietnam / 2
Tanzania + Cambodia / 1
Cambodia+Thailand+Vietnam / 1
Thailand + Cambodia + Vietnam / 1
Thailand+Vietnam / 1

Total Count should equal Total Itineraries.

Any help would be much appreciated!

[Glenn Kennedy]

We can not see what is in which cells. Is 101/Thailand all one cell, for example.

Please read the yellow banner (top) and post a sample sheet.

[harryp]

Hey Glenn, thanks for super quick reply I have added the sheet on the original post.

[davsth]

Not elegant and Im sure Glenn will come up with something more elegant

column D just concatenates the values
Column E finds the last value of the itinerary code (you only want the last value for your results

the pivot shows the count of each order and filters for the 1 in column E

[harryp]

Thank you so much for doing that. It works perfectly and just what I needed!