ExcelBanter - multiplying combinations

Add this function to your workbook:

Code:
--------------------
Public Function NextComboWF(vInx As Variant, n As Long) As Long()
' Worksheet version
' Example usage for 8 choose 4:
' A B C D
' 1 2 3 4 ' literals for first combination
' 1 2 3 5 ' = NextComboWF(A2:D2, 8) array-entered and copied down

' Returns the next combination in lexical order

Dim aiInx() As Long
Dim i As Long
Dim m As Long

m = WorksheetFunction.Count(vInx)

Dim bWrap As Boolean

ReDim aiInx(1 To m)
For i = 1 To m
aiInx(i) = vInx(i)
Next

' set initial combo if empty
If aiInx(1) = 0 Then
For i = 1 To m
aiInx(i) = i
Next i
NextComboWF = aiInx
Exit Function
End If

' find rightmost incrementable index
For i = m To 1 Step -1
If aiInx(i) < n - m + i Then Exit For
Next i

If i = 0 Then
bWrap = True
i = 1
aiInx(1) = 0
End If

' set 'righter' indices sequentially beyond
aiInx(i) = aiInx(i) + 1
For i = i + 1 To m
aiInx(i) = aiInx(i - 1) + 1
Next

NextComboWF = aiInx
End Function
--------------------

In A1, B1, and C1, enter 1, 2, and 3 respectively.

In A2:C2, array enter =NextComboWF(A1:C1, 8) and copy down to row 56.

(Array formulas MUST be confirmed with Ctrl+Shift+Enter, not Enter.
You'll know you did it correctly if curly braces appear around the
formula in the Formula Bar; you cannot type in the braces directly.)

--
shg
------------------------------------------------------------------------
shg's Profile: http://www.thecodecage.com/forumz/member.php?userid=13
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=27126