Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combination of given numbers
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combination of given numbers
Goto Google Groups and paste this into the search box:
Biff "Rendar" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combination of given numbers
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 "Rendar" wrote: 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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combination of given numbers
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. "Rendar" wrote: 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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combination of given numbers
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). "Rendar" wrote: 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. "Rendar" wrote: 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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combination of given numbers
How about if your data set has 519 records and you want all combinations of
numbers that add up to 32347.79? "JMB" wrote: 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 "Rendar" wrote: 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? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combination of given numbers
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. "Dan O''''Connell" wrote: How about if your data set has 519 records and you want all combinations of numbers that add up to 32347.79? "JMB" wrote: 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 "Rendar" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do i zero pad when using combination of text and numbers? | New Users to Excel | |||
matching a combination of numbers | Excel Discussion (Misc queries) | |||
combination of numbers | Excel Discussion (Misc queries) | |||
How to show all the possible combination of a set of numbers? | Excel Worksheet Functions | |||
Total a Combination of Numbers | Excel Worksheet Functions |