How do you sort in Excel using two pairs of cells?

Question

I am working on a spreadsheet where I have certain data organised in this format:

    Row 1: [Train Origin] 
    Row 2: [Train Destination] 
    Row 3: [Train Code]

The items are organised in columns.

What I need is to sort this information by pairs i.e. by one destination and one origin, for example: The first trains that would be listed would be going from destination A to destination B, then from B to A and then from C to D and D to C, etc. Then I would sort by train codes alphabetically. How could this be done using a native function or potentially VBA? I've tried using the native sorting function but without success.

Here's a screenshot of how Excel is currently sorting it:

And how I would like it:

Thanks in advance.

Answer 1

The simplest solution would be to create a helper row with an appropriate key and then sort the table by this key.

The top level order is a pair Origin/Destination or Destination/Origin. One way to construct a group identifier prefix is to join the pair, placing the lexicographically smaller one first and using a separator, which is not used in the table, e.g: both Paris/SPI and SPI/Paris will have a prefix Paris@SPI. To make a key, concatenate this prefix with all the three fields using separator.

For example, assuming that the table is in A1:Z3, type

=if(A1<A2,A1&"@"&A2,A2&"@"&A1)&"@"&A1&"@"&A2&"@"&A3

in A4, copy/drag this formula to the end of the table row

and then use this row to sort the table data:

Sorted table: