Generating subsets of k elements out of a set of n elements

[stpolgar]

In Excel, how do you do generate, for example all subsets of 3 elements out of a set of 10 elements.

[#2]

@stpolgar 

Here is some VBA code adapted from Return all combinations 

'Dimension public variable and declare data type
Public result() As Variant

'Name User Defined Function
Function Combinations(rng As Range, n As Single)
    Dim rng1
    'Save values from cell range rng to array variable rng1
    rng1 = rng.Value
    'Redimension array variable result
    ReDim result(n - 1, 0)
    'Start User Defined Function Recursive with paramters rng1, n, 1, 0
    Call Recursive(rng1, n, 1, 0)
    'Remove a column of values from array variable result
    ReDim Preserve result(UBound(result, 1), UBound(result, 2) - 1)
    'Transpose values in variable result and then return result to User Defined Function on worksheet
    Combinations = Application.Transpose(result)
End Function

'Name User Defined Function and paramters
Function Recursive(r As Variant, c As Single, d As Single, e As Single)
    'Dimension variables and declare data types
    Dim f As Long, g As Long
    'For ... Next statement
    For f = d To UBound(r, 1)
        'Save value in array variable r row f column 1 to array variable result row e and last column
        result(e, UBound(result, 2)) = r(f, 1)
        'If ... Then ... Else ... End If statement
        'Check if variable in e is equal to c -1
        If e = (c - 1) Then
            'Add another column to array variable result
            ReDim Preserve result(UBound(result, 1), UBound(result, 2) + 1)
            'For ... Next statement
            For g = 0 To UBound(result, 1)
                'Save value in array variable result row g second last column to result row g last column
                result(g, UBound(result, 2)) = result(g, UBound(result, 2) - 1)
            Next g
        'Continue here if e is not equal to c - 1
        Else
            'Start User Defined Function Recursive with parameters r, c, f + 1, e + 1
            Call Recursive(r, c, f + 1, e + 1)
        End If
    Next f
End Function

Use like this:

Enter the elements in a range, for example A1:A10.

Select a range large enough for the output (in Excel in Microsoft 365 you only need to select the top left cell).

Enter the formula =Combinations(A1:A10,3)

If you're not on Microsoft 365, confirm the formula with Ctrl+Shift+Enter.