Using macro to concatenate varied outputs from two columns

[pjellytime]

Hello,

As attached, I would like to concatenate values from two columns but add all variations between the values. Would there be a way to create macro to get an output as following?
Note: the order of the output is always "Value"+"|"+"Group"

Thanks for your help!

 

[offthelip]

welcome to the board, try this it uses variant arrays which is much faster than writing directly to the workhseet.

Sub test()
Dim outarr()
lasta = Cells(Rows.Count, "A").End(xlUp).Row
lastb = Cells(Rows.Count, "A").End(xlUp).Row
cola = Range(Cells(1, 1), Cells(lasta, 1))
colb = Range(Cells(1, 2), Cells(lastb, 2))
ReDim outarr(1 To (lasta - 1) * (lastb - 1), 1 To 1)
indi = 1
For i = 2 To lasta
 For j = 2 To lastb
  outarr(indi, 1) = cola(i, 1) & "|" & colb(j, 1)
  indi = indi + 1
 Next j
Next i
Range(Cells(1, 3), Cells((lasta - 1) * (lastb - 1), 3)) = outarr


End Sub

 

[pjellytime]

welcome to the board, try this it uses variant arrays which is much faster than writing directly to the workhseet.

Sub test()
Dim outarr()
lasta = Cells(Rows.Count, "A").End(xlUp).Row
lastb = Cells(Rows.Count, "A").End(xlUp).Row
cola = Range(Cells(1, 1), Cells(lasta, 1))
colb = Range(Cells(1, 2), Cells(lastb, 2))
ReDim outarr(1 To (lasta - 1) * (lastb - 1), 1 To 1)
indi = 1
For i = 2 To lasta
 For j = 2 To lastb
  outarr(indi, 1) = cola(i, 1) & "|" & colb(j, 1)
  indi = indi + 1
 Next j
Next i
Range(Cells(1, 3), Cells((lasta - 1) * (lastb - 1), 3)) = outarr


End Sub

Thank you so much for helping me out. The macro appears to overwrite the top row. Any suggestions on how to resolve this? Thanks again.

 

[offthelip]

change this line:

Range(Cells(1, 3), Cells((lasta - 1) * (lastb - 1), 3)) = outarr

to

Range(Cells(2, 3), Cells((lasta - 1) * (lastb - 1)+1, 3)) = outarr