Spreadsheet : every possible combination of 2 columns into one single colum

Question

and sorry if this question looks familiar, I saw a lot of similar problems but couldn't understand the trick, and I completely stuck.

I have a spreadsheet with 2 columns :

A with Countries
B with their code

  A             B
Belgium        BE
England        EN
France         FR
Luxembourg     LX
Netherlands    NL
Spain          SP

I'm trying to generate generate a matrix representing all the possible travels : Travel from A to A, from A to B, from B to A, and from B to B. The only restriction is not to travel from a country to itself.

At the end I want to generate the result in a single column C, like this :

Travel from Belgium to England
Travel from Belgium to France
Travel from Belgium to Luxembourg
Travel from Belgium to Netherlands
Travel from Belgium to Italy
Travel from Belgium to Spain
Travel from England to Belgium
Travel from England to France
Travel from England to Luxembourg
Travel from England to Netherlands
Travel from England to Italy
Travel from England to Spain
etc
Travel from Belgium to EN
Travel from Belgium to FR
Travel from Belgium to LX
Travel from Belgium to NL
etc
Travel from BE to EN
Travel from BE to FR
Travel from BE to LX
etc
Travel from BE to England
Travel from BE to France
etc

Can anyone help me with that ? I tried several tricks with concatenation and JOIN, but I don't get anywhere ...

Thanks a lot in advance

Use a two dimension array in VBA and loop through. Get started on that and if you run into trouble, come back and edit your question to include your effort and we can help sortt you out. — user4039065

Unknown Unknown · Accepted Answer · 2015-07-18T20:59:15

You can run through each value in column A and associate it with a staggered value from column B but you will have to perform some mathematical trickery using the INT function and MOD function to have the sixth entry in column A associated with the first entry in column B.

INT and MOD to stagger lookup

The formulas in C2:E2 are,

=INDEX(A$2:A$7, INT((ROW(1:1)-1)/(COUNTA(A$2:A$7)-1))+1)
=INDEX(B$2:B$7, MOD(MOD((ROW(1:1)-1), (COUNTA(B$2:B$7)-1))+INT((ROW(1:1)-1)/(COUNTA(B$2:B$7)-1))+1, COUNTA(A$2:A$7))+1)
="Travel from "&C2&" to "&D2

There may be a more elegant way of looping through the destinations while skipping the origin but this works and it is not calculation intensive despite the apparent complexity of the formula(s).

I've used the COUNTA function to determine the number of entries rather than hard-code in ordinal numbers. You should be able to transcribe this for more or less entries by following and modifying the referenced cell ranges.

Spreadsheet : every possible combination of 2 columns into one single colum

1 Answers