On Jul 3, 6:37*pm, "joeu2004" wrote:
"Daka" wrote:
This would be more easiky understood with a copy
of the excel file.

You can upload the Excel file to a file-sharing website and post the URL
(link; http://...) in a response here. *Be sure the uploaded file is marked
shared or sharable. *The following are some free file-sharing websites.

Windows Live Skydrive:http://skydrive.live.com
MediaFihttp://www.mediafire.com
FileFactory:http://www.filefactory.com
FileSavr:http://www.filesavr.com
FileDropper:http://www.filedropper.com
RapidShahttp://www.rapidshare.com
Box.Net:http://www.box.net/files

Daka wrote:
I am in need of help to adjust the code posted
below from an Excel file. The code generate every
possible combination from the value supplied in the
input boxes. *Now, I do not want every combination.
For example if I want to generate the combination
between 1 and 24 numbers in subsets of 8

This description is unclear to me, even with the example. *Do you simply
want all the combinations of 8 from a set of 24 numbers, for example?

Daka wrote:
For example if I want to generate the combination
between 1 and 24 numbers in subsets of 8., the
first few rows of the output should look like this:
12,3,4,5,6,7,8
1,2,3,4,9,10,11,12
1,2,3,4,13.1.4.15.16
1,2,3,4,17,18,19,20
1,2,3,4,21,22,23,24
1,2,3,5,9.13.17.21
1,2,3,5,10,14,18,22

Does it really need to be in that order? *The more natural order is:

1,2,3,4,5,6,7,8
1,2,3,4,5,6,7,9
....
1,2,3,4,5,6,7,24
1,2,3,4,5,6,8,9
1,2,3,4,5,6,8,10
....
1,2,3,4,5,6,8,24

Daka wrote:
This works on the concept that the values in each
subset must not be repeated more than four times
when matched against the preceeding subsets.

Huh?! *In your example, 1, 2, and 3 are all "repeated more than four times"
in preceding subsets. *Are you just trying to suggest an algorithm based on
ignorance of how to generate all combinations of 8 from a set of 24 numbers
(for example)?

The posted code is junk, an obvious hack of something. *It did nothing
useful when I tried it.

-----

The following macro outputs all combinations of K out of N values from
Input!A:A starting in A1 as you did, writing the combinations into
Output!F:F starting in F7 as you did. *I kept the variable names similar to
yours.

The macro intended to be working starting point. *It might even be exactly
what you want. *If not and if you cannot can make the necessary
modifications, let me know what you need (with examples), and perhaps I can
make the changes.

I suggest that you start by executing the macro as-is. *Use a list of 7
numbers or strings in Input!A:A (nFavorites), and enter 3 or 4 for the size
of the subset (nElements).

That keeps the output and runtime manageable. *Note that I artificially slow
down the statusbar update so that you can see that in operation. *For longer
runs, set #Const slowStatus to False. *Eventually, you can remove the code
between #If slowStatus and #EndIf.

***Caveat*** *COMBIN(N,K) grows quite large very quickly. *For example,
COMBIN(24,8) is 735,471. *That exceeds the limits of XL2003. *COMBIN(180,90)
is about 9E+52, which exceeds the limits of anything. *So this approach is
not practical for most sets of "favorites".

-----

Option Explicit

#Const slowStatus = True

Sub combinKofN()
Dim favRng As Range, outRng As Range
Dim chkNum As Long, nFavorites As Long
Dim nElements As Long, maxLen As Long
Dim ofMaxLen As String, s As String
Dim i As Long, j As Long, rowNum As Long
Dim prevPct As Long

On Error GoTo terminate
Application.StatusBar = ""

' column A of sheet "Input" must contain
' data starting in A1, with no interstitial
' empty cells.
' output goes into column F of sheet "Output"

With Sheets("input")
* *Set favRng = .Range("a1", .Range("a1").End(xlDown))
End With
chkNum = favRng.Count

Set outRng = Sheets("output").Range("f7")
outRng.Resize(1, chkNum + 1).EntireColumn.Clear

' allow user to see clear output if sheet
' "output" is active
Application.ScreenUpdating = False

' generate all combinations of nElements of nFavorites

nFavorites = _
* * InputBox("Enter number of favorites", "", chkNum)
If nFavorites <= 0 Or nFavorites chkNum _
* * Then GoTo terminate

nElements = _
* * InputBox("Enter size of subset", "", nFavorites)
If nElements <= 0 Or nElements nFavorites _
* * Then GoTo terminate

maxLen = WorksheetFunction.Combin(nFavorites, nElements)
If outRng.Row + maxLen - 1 _
* * Range("a1").SpecialCells(xlLastCell).End(xlDown).R ow _
* * Then GoTo terminate
ofMaxLen = " of " & maxLen & ": " *' for status

ReDim favorites(1 To nFavorites)
For i = 1 To nFavorites: favorites(i) = favRng(i): Next

ReDim outData(1 To 1, 1 To nElements)
ReDim elements(1 To nElements) As Long
For i = 1 To nElements: elements(i) = i: Next

i = 1: rowNum = 0: prevPct = 0
Do
* * For i = i To nElements
* * * * outData(1, i) = favorites(elements(i))
* * Next
* * rowNum = rowNum + 1
* * outRng.Cells(rowNum).Resize(1, nElements) = outData
* * If rowNum = maxLen Then GoTo terminate

* * ' update Excel status bar by each integer percentage

* * If Int(rowNum / maxLen * 100) prevPct Then
* * * * prevPct = Int(rowNum / maxLen * 100)
* * * * s = prevPct & "% complete, " & _
* * * * * * Format(rowNum, "#,##0") & _
* * * * * * ofMaxLen & outData(1, 1)
* * * * For j = 2 To nElements
* * * * * * s = s & "," & outData(1, j)
* * * * Next
* * * * Application.StatusBar = s
* * * * DoEvents
* * * * #If slowStatus Then
* * * * * * Dim x As Double
* * * * * * x = Timer
* * * * * * Do: DoEvents: Loop Until Timer - x = 0.1
* * * * #End If
* * End If

* * ' next combination

* * i = nElements: j = 0
* * While elements(i) = nFavorites - j
* * * * i = i - 1: j = j + 1
* * Wend
* * elements(i) = elements(i) + 1
* * For j = i + 1 To nElements
* * * * elements(j) = elements(j - 1) + 1
* * Next
Loop

terminate:

Application.StatusBar = ""
Application.ScreenUpdating = True
End Sub

The code you provided worked very well. However you misunderstood what
I am trying to do. The code produces every possible combination. But i
do not want every combination.

Lets work with 24 numbers in sets of 8. We know every possible
combination would be 735,471.

I was able to achieve what I am trying to do by ajusting the code to
loop thru the subsets on the outputsheet but this was extremely slow
approach.
The first line would be:
1,2,3,4,5,6,7,8

For the second line or subset the code would compare the second subset
with whats already on the output sheet. 1,2,3,4,5, 6, 7, 9 would be
inelligible because becuse 7 of the nubers would be repeated, we only
want 4. So the next elligible subset would be 1,2,3,4,9,10,11,12 the
next would be 1,2,3,4,13,14,15,16.
As I stated before I was able to achieve this by looping thru the code
on the output sheet but it was extremely slow. It produced
approximately 759 subset.
Having the alogarithm adjusted to do this would be far more efficient
if it is possible to have it done this way.
Thanks
Derick