Need to create a list of all possible combinations

[LisaGarvey]

I have a list of 50 unique values and I need to generate a full list of all possible combinations for these values - with the exception of like value to like value. Is there an easy way to do this?

Example:
Starting list

AA
BB
CC

Will generate a list of

AA to BB
AA to CC
BB to AA
BB to CC
CC to AA
CC to BB

Thanks,
Lisa

 

[XLfriend777]

Hi LisaGarvey - Welcome to the forum. I don't have much time now, but this may help you get started. Try the code:

Sub LisaGarvey_List50()
Dim i As Integer
i = 1
While i < 50
Cells(i, 2).Value = Cells(1, 1).Value + " to " + Cells(i + 1, 1).Value

i = i + 1
Wend
End Sub

 

[derHoepp]

Hi there!

you could also use Formulas to get the combinations. Either using a cross-tab referencing 1 to 50 columns and 1 to 50 rows, or calculating them just in one column. I used two sheets (Sheet2 and Sheet3) to showcase the two different versions. If you need to combine Strings, you could extend the Formula with an Index(Match()) or VLookup.

Option Explicit

Sub fillCombi()
    Worksheets("Sheet2").Range("A1").Resize(50, 50).Formula = "=Row() & "" to "" & Column()"
    Worksheets("Sheet3").Range("A1").Resize(50 * 50, 1).Formula = "=(MOD(ROW()-1,50)+1) & "" to "" & INT((ROW()-1)/50)+1"
End Sub

happy combining!
derHoepp

 

[derHoepp]

Hello again,

i tweaked my solution a bit and included two more requirements. The first one is that instead of combining the numbers, they get looked up in a list of Items (it is just a named range of all items in one column). the second one is that combinations of the same items are ignored:

Sub fillCombinations()
    'ListOfItems is a named Range on a worksheet containing 50 cells with information
    Worksheets("Sheet3").Range("A1").Resize(50 * 50, 1).Formula = "=IF(MOD((ROW()+51)-1,51)>0,INDEX(ListOfItems,(MOD(ROW()-1,50)+1)) & "" to "" & INDEX(ListOfItems,INT((ROW()-1)/50)+1),"""")"
End Sub

Instead of using it as a Macro, you can also just type in the formula in the cells A1 to A2500.
Good luck!
derHoepp

 

[LisaGarvey]

Hi there!

you could also use Formulas to get the combinations. Either using a cross-tab referencing 1 to 50 columns and 1 to 50 rows, or calculating them just in one column. I used two sheets (Sheet2 and Sheet3) to showcase the two different versions. If you need to combine Strings, you could extend the Formula with an Index(Match()) or VLookup.

Option Explicit

Sub fillCombi()
    Worksheets("Sheet2").Range("A1").Resize(50, 50).Formula = "=Row() & "" to "" & Column()"
    Worksheets("Sheet3").Range("A1").Resize(50 * 50, 1).Formula = "=(MOD(ROW()-1,50)+1) & "" to "" & INT((ROW()-1)/50)+1"
End Sub

happy combining!
derHoepp

Hello,

Thank you for the quick response! The second (Sheet3) code was the closest to what I am looking for. I ran the macro and it did give me all of the combinations - but of numbers and not the values I had in Sheet1. I also gave bad examples... I am looking to generate the results in two columns.

CA10
US10
US11
DE10

Would give me:

US10 CA10
US11 CA10
DE10 CA10
CA10 US10
US11 US10
DE10 US10
CA10 US11
US10 US11
DE10 US11
CA10 DE10
US10 DE10
US11 DE10

The columns can switch between A & B, but I need to pull from a specific set of data and get two columns for the results (although the length of the values are fixed so I can do a quick LEFT and RIGHT to separate if it's easier to put them together in one column). I should not have included the "to". I'll see if I can work from what you've provided, but I'm a SQL girl and VBA is still new to me.

Thanks!
Lisa

 

[derHoepp]

Hi Lisa,

then my second post should be even closer to what you were looking for. As I don't know, where you put the source of your item, I have to assume, you created a named Range "ListOfItems". To seperate the Result, just put both parts in two different Columns:

Sub fillCombinations()
    'ListOfItems is a named Range on a worksheet containing 50 cells with information
    Worksheets("Sheet3").Range("A1").Resize(50 * 50, 1).Formula = "=IF(MOD((ROW()+51)-1,51)>0,INDEX(ListOfItems,(MOD(ROW()-1,50)+1)),"""")"
    Worksheets("Sheet3").Range("B1").Resize(50 * 50, 1).Formula = "=IF(MOD((ROW()+51)-1,51)>0,INDEX(ListOfItems,INT((ROW()-1)/50)+1),"""")"
End Sub

Hope this helps. By the way, I use VBA to Fill a big range in just one step. But still the work is done by formulas. You could also just select 2500 cells of Column A and B and fill in the formulas by hand.

derHoepp

 

[LisaGarvey]

Hi Lisa,

then my second post should be even closer to what you were looking for. As I don't know, where you put the source of your item, I have to assume, you created a named Range "ListOfItems". To seperate the Result, just put both parts in two different Columns:

Sub fillCombinations()
    'ListOfItems is a named Range on a worksheet containing 50 cells with information
    Worksheets("Sheet3").Range("A1").Resize(50 * 50, 1).Formula = "=IF(MOD((ROW()+51)-1,51)>0,INDEX(ListOfItems,(MOD(ROW()-1,50)+1)),"""")"
    Worksheets("Sheet3").Range("B1").Resize(50 * 50, 1).Formula = "=IF(MOD((ROW()+51)-1,51)>0,INDEX(ListOfItems,INT((ROW()-1)/50)+1),"""")"
End Sub

Hope this helps. By the way, I use VBA to Fill a big range in just one step. But still the work is done by formulas. You could also just select 2500 cells of Column A and B and fill in the formulas by hand.

derHoepp

Thank you!! This is absolutely perfect and exactly what I needed. You've saved my Canadian bacon with this. I really appreciate your quick response!

Lisa

 

[derHoepp]

Glad I could help, thanks for your feedback.