concatenating with unequal columns

[Guest]

I have 3 columns of data that I would like to concatenate to end up with
something like this: T1 APPLE AR
T1 ORANGE AR
T1 BANANA AR and so on, then
T2 APPLE AR
T2 ORANGE AR, etc. showing every possibility of these 3
columns:

Table Product State
T1 APPLE AR
T2 ORANGE NY
T3 BANANA FL
T4 GRAPES
T5 TOMATO
T6
T7

Can anyone help me with this?

Thanks! (in advance)

 

[Guest]

You could use a macro to do this. Assuming your data is in Columns A,B and C
with the headings in row 1 and the data starting in row 2 then the macro
below will place the concatenations in column E starting in Row 2.

Sub Concat()

Dim Table() As Variant
Dim Product() As Variant
Dim State() As Variant
Dim endRow As Long
Dim i As Long
Dim j As Long
Dim k As Long
Dim counter As Long

endRow = Cells(Rows.Count, 1).End(xlUp).Row
Table = Range(Cells(2, 1), Cells(endRow, 1)).Value

endRow = Cells(Rows.Count, 2).End(xlUp).Row
Product = Range(Cells(2, 2), Cells(endRow, 2)).Value

endRow = Cells(Rows.Count, 3).End(xlUp).Row
State = Range(Cells(2, 3), Cells(endRow, 3)).Value

counter = 2
For i = 1 To UBound(Table)
For j = 1 To UBound(Product)
For k = 1 To UBound(State)
Cells(counter, 5).Value = Table(i, 1) _
& " " & Product(j, 1) & " " & State(k, 1)
counter = counter + 1
Next k
Next j
Next i

End Sub

Regards
Rowan

 

[Guest]

Excellent!! Worked like a charm! You're awesome!