Search code examples
arraysexcelconcatenationalphabetical-sort

Concatenating two columns of text Alphabetically in Excel

I am working with this spreadsheet of airport data.Sorry can't figure out how to format the headers, but first column is Origin IATA, 2nd is Dest IATA, and 3rd if Concatenate Route 

Origin IATA,       Dest IATA,       Concatenate Route

LHR                JFK              LHR-JFK
JFK                LHR              JFK-LHR
CAN                PEK              CAN-PEK
PEK                CAN              PEK-CAN
JFK                LAX              JFK-LAX
LHR                DXB              LHR-DXB
DXB                LHR              DXB-LHR

Picture version:

enter image description here

Right now, I just have =CONCATENATE(C:C,"-",D:D) as the formula, but I actually need them to concatenate in alphabetical order so I can collapse CAN-PEK as one round trip route.

I found someone with a similar question here: Excel formula to take values from cell range, sort alphabetically and write as a single string

And I tried to modify the code of the accepted answer, but I keep getting errors because I don't know what I'm doing. I also tried VBA code, doing it in access, and many other Stackoverflow solutions, but I am stuck.

Does anyone know how I need to write the code to get it to do what I want?

Thank you!

Solution

  • With Excel one can use mathematical inequalities for text strings in many cases. In C2 put:

    =IF(A2<B2,A2&"-"&B2,B2&"-"&A2)

    Then copy down

    enter image description here