Concatenate Lists of Different Lengths

[buckyzacker]

Hi All,

I have three lists:

Column A: 50 state names
Column B: List of types of autos (private passenger, light, medium, heavy, extra-heavy)
Column C: List of numbers (100, 200, 300, 400, 500, 600, 700, 800, 900, 1000).

I want to concatenate the three lists together to create unique identifiers so that there is a state/type/number for each possibility, which in this case would be 50x5x10=2,500 possibilities.

For example, the the results would start out:
Alabamaprivatepassenger100
Alabamaprivatepassenger200
Alabamaprivatepassenger300

and end with

Wyomingextra-heavy1000.

I appreciate your help.

Thanks.

 

[dat789]

I don't have the answer for your question, buckyzacker. However, the resulting output you seek looks VERY familiar and I know for a fact that that can easily be achieved using SQL's (I use MySQL) Cartesian product joins.
This simply means the 3 tables (your column A, B, C is now a table each) joins up to every row of the other table.
This can be achieved with less than 10 lines of coding.

I hope this offers you an idea

 

[kpark91]

Sub createAllUniquePossibilities()
    Dim i&, j&, k&, count&
    count = 1
    For i = 1 To Range("A" & Rows.count).End(xlUp).Row
        For j = 1 To Range("B" & Rows.count).End(xlUp).Row
            For k = 1 To Range("C" & Rows.count).End(xlUp).Row
                Range("D" & count).Value = Range("A" & i).Value & Range("B" & i).Value & Range("C" & i).Value
                count = count + 1
            Next k
        Next j
    Next i
End Sub

O(n^3)
Put this in your worksheet module containing the information.
Outputs on column D starting from row 1.

 

[andrewkard]

Hello, maybe so:

Sub test()
    Dim l&, i&, lLr$, lCn&
    lCn = 1
    lLr = Cells(Rows.count, "A").End(xlUp).Row
    For l = 1 To lLr
        For i = 1 To lLr
            Cells(lCn, 4) = Cells(l, 1) & Cells(l, 2) & Cells(i, 3)
            lCn = lCn + 1
        Next i
    Next l
End Sub

 

[dat789]

Wow! Nice one.
didn't know macros can do this.