Combination of given numbers

[Guest]

Hello,
I have a list of twelve distinct numbers and would like to somehow display
all distinct combinations of 6 on Excel. The COMBIN function just gives me
the total number of them (924). Is there a way to explicitly show them?

 

[T. Valko]

Goto Google Groups and paste this into the search box:

(e-mail address removed)

Biff

 

[Guest]

You could try a macro. Assuming the 12 items are on Sheet1 in cells A1:A12
and you want the list on Sheet2, starting in cell A2 - this appears to give
the results you want:

Sub ListCombinations()
Dim rngList As Range
Dim wksDest As Worksheet
Dim a As Long, b As Long, c As Long
Dim d As Long, e As Long, f As Long
Dim lngElements As Long

Set rngList = Sheet1.Range("A1:A12")
Set wksDest = Sheet2
lngElements = rngList.Cells.Count

For a = 1 To lngElements - 5
For b = a + 1 To lngElements - 4
For c = b + 1 To lngElements - 3
For d = c + 1 To lngElements - 2
For e = d + 1 To lngElements - 1
For f = e + 1 To lngElements
wksDest.Cells(Rows.Count, 1).End(xlUp)(2, 1).Value = _
rngList(a) & ", " & rngList(b) & ", " & rngList(c) & ", " & _
rngList(d) & ", " & rngList(e) & ", " & rngList(f)
Next f
Next e
Next d
Next c
Next b
Next a
End Sub

 

[Guest]

Thank you very much it works.
Although ,I forgot to mention that I would like to see the other 6 numbers
not in the group of 6 as well for each combination.

 

[Guest]

If the list is in A2:A925, try this in B2 and copy down

=INDEX(A$2:A$925,COUNTA(A2:A$925))

So for A2 it will return the item in A925, for A3 it will return A924 (ie
the formula takes the existing list and inverts the order which I believe
will give you want you want).

 

[Dan O''''Connell]

How about if your data set has 519 records and you want all combinations of
numbers that add up to 32347.79?

 

[JMB]

I think that many records will require more power than XL can bring to the
table. I'd expect XL will crash.

For XL combination solutions, I think most folks recommend Harlan's macro.
Go to Google and search on "findsums". The only caveat is if the first n
numbers in your series happen to total the number you want, I think it misses
that particular combination. Also, I think it stops after it fills up an
entire column of one worksheet.

But that will most likely work only for a *much* smaller data set. Most XL
solutions I've seen are only for data sets around 30.