Excel VB code for unique non-repeating combinations

[Rickfr39]

Hi,


I have 10 text strings (for this example will use colors as listed below) that I want to combine in unique non-repeating combinations from 2 to 10. (More info below colors)


Red

Green

Yellow

Blue

White

Black

Orange

Purple

Grey

Brown


So what I mean is that I would like combo's as follows:


red-green

red-yellow

...other 2 string combos

red-green-yellow

red-green-blue

....other 3 string combos

red-green-yellow-blue

red-green-yellow-white

...other 4 string combos

...all the way to 10 string combo


I have found code that will do this, the problem is that I get combo's that repeat (but in different order), like:


red-green

green-red

red-green-yellow

yellow-green-red


The key here is that the order doesn't matter, want only one instance of the colors combined for each 2,3,4, 5, etc.. value sets. I am not much of a math whiz, but believe this has to do with factorials, so the following table should represent the total number of non-repeating combinations that there would be:


10 item Combo = 1 unique combo

9 item Combo = 10 unique combos

8 item Combo = 45 unique combos

7 item Combo = 121 unique combos

6 item Combo = 210 unique combos

5 item Combo = 252 unique combos

4 item Combo = 210 unique combos

3 item Combo = 120 unique combos

2 item Combo = 45 unique combos

1 item = 10 items listed


Total unique combinations= 1,024


Any help on how to code this would be greatly appreciated.


Thanks,


Rick

 

[Luke M]

Could you post the code that you have already? I believe your count is correct, but seeing an example of how to handle the different size combos could save us some time.

So far, I've only got coding to handle 2 string combo.

[pre]

Sub GenerateCombos()
Dim ValueList As Range
Dim x As Integer
Dim i As Integer
Dim Combos() As Variant
Dim xCount As Integer
Dim xColumn As String

Set ValueList = Range("A2:A5")
'What row and column do you want new list ouput to?
xCount = 1
xColumn = "E"

Application.ScreenUpdating = False
For i = 1 To ValueList.Count - 1
For x = 1 To ValueList.Count - i
Cells(xCount, "E") = ValueList.Cells(i, 1) & "-" & ValueList.Cells(i + x, 1)
xCount = xCount + 1
Next x
Next i

Application.ScreenUpdating = True
End Sub

[/pre]

 

[Rickfr39]

Hi Luke,


Thank you for your input. I think I got this working, reran and got the correct numbers for each of the 9 combinations. Here is what I have in case your interested.


Sub test()

ListCombos Range("A1:A10"), 5, 389

'first number after "," indicates how many items each output set will contain, second number denotes what row in column A to output to

End Sub


Sub ListCombos(r As Range, m As Long, iRow As Long)

' lists the combinations of r choose m starting in row iRow

Dim ai() As Long

Dim i As Long

Dim n As Long

Dim vOut As Variant


n = r.Rows.Count


ReDim ai(1 To m)

ReDim vOut(1 To m)


ai(1) = 0

For i = 2 To m

ai(i) = i

Next i


Do

For i = 1 To m - 1

If ai(i) + 1 < ai(i + 1) Then

ai(i) = ai(i) + 1

Exit For

Else

ai(i) = i

End If

Next i

If i = m Then

If ai(m) < n Then

ai(m) = ai(m) + 1

Else

Exit Sub

End If

End If


' put the values in the variant

For i = 1 To m

vOut(i) = r(ai(i))

Next i

' list it

Cells(iRow, 1).Resize(, m).Value = vOut

iRow = iRow + 1

Loop

End Sub