I have a dataset that has the following columns and I have populated some rows.
I want to find a way that would count the most popular route, i.e the most occurrences of two matching cells across multiple rows. In this example it would be edinburgh and glasgow as that is the route not to show lax even though it appear twice.
Is there a way in excel to do this?
To count routes for each row use:
=COUNTIFS(B:B,B2,C:C,C2)
to get first most popular - use array formula:
=INDEX(B1:B8 &"-" & C1:C8,MATCH(MAX(COUNTIFS(B1:B8,B1:B8,C1:C8,C1:C8)),COUNTIFS(B1:B8,B1:B8,C1:C8,C1:C8),0))