Urgently trying to count unique pairs between 2 columns of text

Hi there,

I have 2 columns of text which include the origins and destinations of an airline and I want to count the number of unique routes (ie. A to B and B to A should count as one).

If the network was simple, and the destinations for each airport were the same as its origin airports, it's simple (I just count the number of total routes and halve it). But in this case, an airport "A" might have a flight to "B" and then the plane goes to "C" before returning to "A" - so I can't assume that "A" and "B" connect in both directions.

Can anyone help? Would be really appreciated

I think you'll need to attach a sample of your data.

Usually with things like this it's easiest to just join the values together to create unique strings to count on.

Hi @echopassenger,

Hopefully image is attached. The list is a LOT longer than the attached example, but it describes the issue well. Note that Airport B flies to C, but Airport C doesn't fly to B. I want to be able to count all the unique routes (eg. in this example, there are 9 rows of data and 8 unique routes because A-B and B-A should be counted as one)

excel.png

Hi @echopassenger,

This is EXACTLY what I was looking for and is working perfectly. Thanks very very much for your help, your input has saved me hours of wasted time and made this task a whole lot easier!

Thanks!!

No problem, good luck with the rest of your work.

Hi @EchoPassenger,

I was hoping to ask you for one more morsel of advice... basically I have 2 datasets.

The first is the airports origins and destinations of Airline A and the number of seats flown on these, the second is the same for Airline B.

I then create two further datasets by assigning a city to each of Airline A and B's airports.

By using your tricks from your spreadsheet above, I have managed to calculate the presence of Airline B on Airline A's airport network when the two are flying to the same airports (using a match function to see if a match is true, and then if it is just taking in the number of seats that Airline A flies on that route; adding all these true matches and getting that as a percentage of overall seats flown).

However, when I have cities, I find regularly more than one match (eg. say Airline A flies from 3 airports in London into 2 airports in Milan and Airline B flies from all 4 airports in London into all 3 airports in Milan). In these cases, my function will only return the figure from the first incidence where "London" and "Milan" matched but not the second or third, etc.

So basically I was wondering would you be able to advise on how I can get my matching function to return the second and third incidences of matching (rather than the first repeatedly)... or indeed whether I should just find a way of tallying up all the times that Airline A flies from simply "London" into "Milan" (the problem with this procedure is that the body of data is quite significant).

Thanks for any help!!