PDA

View Full Version : [SOLVED] Concatenate Transpose - Multiple with Multiple



dj44
04-25-2018, 08:17 AM
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
04-26-2018, 05:17 AM
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
04-26-2018, 06:01 AM
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!!