Concatenate permutations between two columns

Question

I need help with an excel assignment.

Name    City
---------------
John    London
Maxx    NY
Ashley  DC
        Paris

Solution for this must be:

John-london
John-NY
John-DC
John-Paris
Maxx-london
Maxx-NY
.
.
.
.so on.

Simply I have to add text of all elements in one column to text of all elements in other column. I will appreciate if a solution without macros or VB is provided.

Answer 1

Here's an Array Formula you can use, though you will need to modify the size of the matrix depending on how many entries you have

=CONCATENATE(INDEX(A:A,MMULT(ROW(A1:A3),TRANSPOSE(ROW(B1:B4))/TRANSPOSE(ROW(B1:B4)))),"-",INDEX(B:B,MMULT(ROW(A1:A3)/ROW(A1:A3),TRANSPOSE(ROW(B1:B4)))))

Assuming Column A is Names and Column B is Cities, you would select 12 cells (3 rows high, 4 columns wide), paste the above formula in the first cell and press Ctrl + Shift + Enter to create the array formula.

If you want to see a little simpler version to see what it does before the INDEX references, check with the same area:

=CONCATENATE(MMULT(ROW(A1:A3),TRANSPOSE(ROW(B1:B4))/TRANSPOSE(ROW(B1:B4))),"-",MMULT(ROW(A1:A3)/ROW(A1:A3),TRANSPOSE(ROW(B1:B4))))

Here's a screenshot (with the formula split in 2 lines) of the single formula displaying the output over multiple cells:

Answer 2

This is a simple example in VBA. It is intended to show the concept, not the best practices. Please use it to get you started, and get back here if you need more help, if you want to improve the performances, etc.

The example assumes that the two lists are in A1:An and B1:Bm, and the resulting list goes in column C.

Sub Test()
  Dim R1 As Integer, R2 As Integer, R As Integer, NR As Integer
  NR = ActiveSheet.UsedRange.Rows.Count
  Columns(3).Clear
  For R1 = 1 To NR
    If Not IsEmpty(Cells(R1, 1)) Then
      For R2 = 1 To NR
        If Not IsEmpty(Cells(R2, 2)) Then
          R = R + 1
          Cells(R, 3) = Cells(R1, 1) & " - " & Cells(R2, 2)
        End If
      Next R2
    End If
  Next R1
End Sub

Answer 3

resulting column formula should be

=column1&"-"&column2

Answer 4

You can use this formula (start in Row 1 and fill down until you run out of combinations):

=IFERROR(INDEX(L_1, CEILING(ROW()/COUNTA(L_2),1)  ,1) & "-" & 
         INDEX(L_2, 1+MOD(ROW()-1, COUNTA(L_2))   ,1), "That's it!")

I'm using named ranges "L_1" and "L_2" to refer to the first and second lists respectively