Need to create matched pairings between 2 columns

[Technetium]

I have column A and column B that both have a list of names. I need to be able to create a list of all pairings names from A with names from B, and has to work for different numbers of names in each column. Ideally I want it set up so that I can add or delete a name from either column, run the VBA macro, and it will update all the pairings. The example below should show what I mean for the output:

Well, it would, but it seems I can't directly paste it from the clipboard. I attached a file. If the description of what I want isn't clear above, hopefully someone takes the time to look at the file.

I don't believe it is possible to do this without VBA, but I have been wrong about some other similar assumptions before, so if it can be done with normal Excel code, then that would definitely be preferable. Thanks! I'm pretty new to VBA, and while I tried to look up some examples of writing VBA code, I couldn't find anything that seemed remotely like this.

[Artik]

e.g.

Sub AAA()
  Dim vColA
  Dim vColB
  Dim vMatchups As Variant
  Dim lRow As Long
  Dim rA As Long, rB As Long
  Dim i As Long
  
  lRow = Cells(Rows.Count, "A").End(xlUp).Row
  vColA = Range("A2:A" & lRow).Value
  
  lRow = Cells(Rows.Count, "B").End(xlUp).Row
  vColB = Range("B2:B" & lRow).Value
  
  ReDim vMatchups(1 To UBound(vColA) * UBound(vColB), 1 To 2)
  
  For rA = 1 To UBound(vColA)
    For rB = 1 To UBound(vColB)
      i = i + 1
      vMatchups(i, 1) = vColA(rA, 1)
      vMatchups(i, 2) = vColB(rB, 1)
    Next rB
  Next rA
  
  Range("D2:E2").Resize(UBound(vMatchups)).Value = vMatchups
End Sub

Artik

[Technetium]

Thank you very much. Since I am new to VBA it took me a little while to figure out what everything meant, but I did figure it out and was able to adapt it to my specific circumstance ( I did not need UBound or Rows.Count because there's an Excel cell that already has that calculated).

[torachan]

good old fashioned loops - easier to understand.
torachan.

Option Explicit
Dim x As Long, y As Long, r1 As Long, r2 As Long, ctr As Long

Sub MATCHUP()
x = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row
y = Sheet1.Cells(Rows.Count, "B").End(xlUp).Row
ctr = 2
For r2 = 2 To x
For r1 = 2 To y
Sheet1.Cells(ctr, 8) = Sheet1.Cells(r2, 1)
ctr = ctr + 1
Next r1
Next r2
ctr = 2
For r2 = 2 To x
For r1 = 2 To y
Sheet1.Cells(ctr, 9) = Sheet1.Cells(r1, 2)
ctr = ctr + 1
Next r1
Next r2
End Sub

[Artik]

good old fashioned loops - easier to understand.

But several times slower in action

Artik

[torachan]

I did not see 10,000 rows of data in the example therefore speed did not appear of the essence.
Do not blink as you will not see the time difference.

Attachment 738162
Attachment 738163

[Artik]

OK, but by not practicing using arrays, we try to avoid such solutions. Someday it will be time to use 10k, 100k, 1M and what? We'll use cell loops because that's the only thing we know how to do. The forums are full of suboptimal code. We are to learn and not duplicate old uneconomical solutions.
It's so off topic.

Artik