Function GENCOMB(N As Double,T As Double) As Variant
Rem Return array of combinations of the set 0 to N-1, taken T at a time
Rem The array will contain =COMBIN(N;T) rows and T columns
Rem When called from Calc, end the =GENCOMB(N;T) formula with
Rem ⌘⇧Enter on a Mac, Ctrl+Shift+Enter on other platforms
Rem For example, =GENCOMB(4;2) is 1;0|2;0|2;1|3;0|3;1|3;2 (6 rows, 2 columns)
Rem Knuth, The Art of Computer Programming, Section 7.2.1.3, Algorithm T
If N <= 0 Then GENCOMB = "Set size is not positive" : Exit Function
If T <= 0 Then GENCOMB = "Subset size is not positive" : Exit Function
If T > N Then GENCOMB = "Subset size large than set size" : Exit Function
Dim I As Long, J As Integer, K As Integer, X As Integer, R As Long
Dim G() As Integer, C() As Integer
N = Int(N) : T = Int(T) : R = N
For I = 2 To T : R = R*(N-I+1)/I : Next I
ReDim G(1 To R,1 To T) As Integer, C(1 To T+2) As Integer
For J = 1 To T : C(J) = J-1 : Next J
C(T+1) = N : C(T+2) = 0 : J = T
For I = 1 To R
For K = 1 To T : G(I,K) = C(T+1-K) : Next K
Select Case True
Case J>0 : C(J) = J : J = J-1
Case C(1)+1<C(2) : C(1) = C(1)+1
Case Else : J = 2
Do
C(J-1) = J-2 : X = C(J)+1 : J = J+1
Loop While X=C(J)
C(J-1) = X : J = J-2
End Select
Next I
GENCOMB = G
End Function
For example, the array formula =GENCOMB(4;2) will generate the six rows:
1 0 2 0 2 1 3 0 3 1 3 2
If you prefer numbering to begin with 1 instead of 0, use array formula =1+GENCOMB(4;2) or =4-GENCOMB(4;2).
Changing statement G(I,K) = C(T+1-K) to G(R+1-I,K) = N-C(T+1-K) will generate this set of combinations:
1 2 1 3 1 4 2 3 2 4 3 4
The number of rows returned by =GENCOMB(N;T) is the value of the standard Calc function =COMBIN(N;T). Even small parameters can produce many rows. For example, =COMBIN(20;10) is about 185,000. But the algorithm GENCOMB uses is efficient, and my system can execute =GENCOMB(20;10) in less than 30 seconds.
[Tutorial] How to install a code snippet