Create concatenated list of unique IDs from multiple ranges

[Yubajack]

I have several lists named as ranges and want to create a single list of all the unique combinations of items on those lists with a formula ideally:

For example, if I had three lists with the following values:

List 1: A, B
List 2: 1, 2
List 3: X, Y

The formula would create this list:
A1X
B1X
A2X
B2X
A1Y
B1Y
A2X
A2Y

Thank you!

 

[baitmaster]

welcome to the board

Difficult to do this with a formula because the number of cells you're writing to will vary depending on the length of your lists - anything dynamic usually requires macros. I'm not saying it's impossible, but my first port of call would always be a macro for something like this

The following code will take the values of three named ranges (I've called them range1 to range3), and then writes the results to cell E1. Change all of these values in the code as required. Let me know if you need help adding this code to your file and then running it

Option Explicit

Sub mergeLists()


' create range objects
Dim rng1 As Range: Set rng1 = Range("range1")
Dim rng2 As Range: Set rng2 = Range("range2")
Dim rng3 As Range: Set rng3 = Range("range3")


' create results variables
Dim iArrSize As Integer: iArrSize = rng1.Cells.Count * rng2.Cells.Count * rng3.Cells.Count
Dim arrResults(): ReDim arrResults(1 To iArrSize, 1 To 1)


' create processing variables
Dim cl1 As Range, cl2 As Range, cl3 As Range
Dim iCount As Integer


' loop through each item in each list and pass concatenated value to next row of array
For Each cl1 In rng1
    For Each cl2 In rng2
        For Each cl3 In rng3
            
            iCount = iCount + 1
            arrResults(iCount, 1) = cl1 & cl2 & cl3
            
        Next cl3
    Next cl2
Next cl1


' pass results back to worksheet
Range("E1").Resize(iArrSize, 1).Value = arrResults


End Sub

 

[Yubajack]

Thanks! That did the trick!

 

[Eric W]

Just FYI, it can be done with formulas. Since you have an answer you're happy with, I won't elaborate, but look at post 4 in this thread:

https://www.mrexcel.com/forum/excel...ed-combinations-formula-possible-outfits.html

It dynamically creates combinations for up to 5 columns, with a variable number of items per column. Let me know if you have questions.