Concatenate Transpose - Multiple with Multiple

[dj44]

Good day folks,

i am needing some transpose and concatenation help.

Car	A	Car A	House A
House	B	Car B	House B

I wanted to create some results.

Each item in column A with each item in column B

so that it looks like the above

But i have failed to transpose and concatenate effectively.

Or is this better if i create a formula that i insert with vba?

[mana]

 Option Explicit


 Sub test()
    Dim v()
    Dim n As Long
    Dim i As Long, j As Long
    
    With Cells(1).CurrentRegion
        n = .Rows.Count
        ReDim v(1 To n, 1 To n)
        For i = 1 To n
            For j = 1 To n
                v(j, i) = .Cells(i, 1).Value & " " & .Cells(j, 2).Value
            Next
        Next
    End With
    
    Cells(4).Resize(n, n).Value = v
    
 End Sub

[dj44]

Thank you Mana,

that is amazing!!!

I can put away all those horrid concatenation formulas. they were long

=TRANSPOSE(CONCATENATE(“A2″,”A3″,”A4″,”A5″,”A6″,”A7”.....and something else

I did do some multiplication but that was as far as i could go

Thank you very much for this mathematical wizadry

I have a long column of things to combine now - happy days

Have a great day!!