Listing down combinations in excel (nCr)

[playamaker]

Hi,

I want to list down all the possible combinations of size 'r' from a list of size 'n'. So let's say I have the following list from cell A1 to cell A6.

A
B
C
D
E
F

I want to list down combinations of size 4 starting in column C. So cell C1 to C4 will be:

A
B
C
D

Then cells D1 to D4 will be:

A
B
C
E

and so on till all the 15 combinations of size 4 are listed, with the last combination in column Q.

 

[Trebor76]

Hi playamaker,

Welcome to MrExcel!!

Based primarily on the function written by Weaver here, this macro will do the trick:

Option Explicit
Sub ListAllCombinations()
    
    Dim x As Long
    Dim rs As Recordset
    Dim intNumFrom As Integer
    Dim intNumTo As Integer
    Dim intNumInDraw As Integer
    Dim xlnCalcMethod As XlCalculation
            
    With Application
        xlnCalcMethod = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .StatusBar = "Please wait while the draws are outputted..."
    End With
    
    intNumFrom = 1 'Starting number / row
    intNumTo = 6 'Ending number / row
    intNumInDraw = 4 'Number on draw
    
    Range("C1").Select 'Initial output cell.  Change to suit if necessary.
    x = doTheLott("", CStr(intNumFrom), intNumFrom, rs, intNumFrom, intNumTo, intNumInDraw)
    
    With Application
        .Calculation = xlnCalcMethod
        .StatusBar = ""
        .ScreenUpdating = True
    End With
    
    MsgBox Evaluate("COMBIN(" & intNumTo & "," & intNumInDraw & ")") & " draws have now been outputted."
    
End Sub
'Following User Defined Function (UDF) based on Weaver's solution from here:
'https://www.mrexcel.com/forum/excel-questions/486259-all-lottery-combinations-1-49-a-2.html
Function doTheLott(ByVal xStr As String, r As Long, a As Integer, rs As Recordset, intNumFrom As Integer, intNumTo As Integer, intNumInDraw As Integer) As Long
    
    Dim xArr As Variant, i As Integer
    Dim f As Variant
    Dim lngMyRow As Long
    
    xArr = Split(Trim(xStr), " ")
    
    If UBound(xArr) = intNumInDraw - 1 Then
        For i = 0 To intNumInDraw - 1
            ActiveCell.Offset(i, 0) = Range("A" & xArr(i)).Value
        Next i
        ActiveCell.Offset(0, 1).Select
        r = r + 1
    Else
        For i = a To intNumTo
            r = doTheLott(xStr & i & " ", r, i + 1, rs, intNumFrom, intNumTo, intNumInDraw)
        Next
    End If
    
    doTheLott = r
    
End Function

Note I used the row numbers (1 to 6 inclusive) as the basis of the three inputs for the doTheLott function as they need to be numeric. As I didn't write the function I can't help too much on it I'm afraid.

HTH

Robert

 

[shg]

There's a workbook at https://app.box.com/s/b9b9fc06beb63b9562f9 that does this:

A B C D
A B C E
A B C F
A B D E
A B D F
A B E F
A C D E
A C D F
A C E F
A D E F
B C D E
B C D F
B C E F
B D E F
C D E F