How Do I Get Excel To Generate All Possible Numerical Combinations?

[anonymouspottamus]

I'm new to excel. I'm trying to generate all possible number combinations for the problem below:

Generate all numerical combinations when selecting a team of 5 people from a total of 20 candidates (range is 1-20), and 5 are being selected at a time
Rule 1: no repetition is allowed
Rule 2: order does not matter

Please note, the answer to =COMBIN(20,5) is not the complete answer to this question. The answer is supposed to result in a list of each of the 15.504 possible "team" combinations per my assignment. I don't know how to to use excel to generate that list.

Step-by-step guidance would really be appreciated.

 

[BigBeachBananas]

How about this?

Sub GenerateCombinationsToSheet()
    Dim Candidates() As Integer
    Dim Combination() As Integer
    Dim NumCandidates As Integer
    Dim NumCombination As Integer
    Dim i As Integer, j As Integer
    Dim OutputSheet As Worksheet
  
    NumCandidates = 20
    NumCombination = 5
  
    Set OutputSheet = ThisWorkbook.Sheets("Sheet1") '<~~ Change sheet as needed
  
    ReDim Candidates(1 To NumCandidates)
    For i = 1 To NumCandidates
        Candidates(i) = i
    Next i
  
    ReDim Combination(1 To NumCombination)
  
    OutputSheet.Cells.Clear
  
    CombinationsRecursive OutputSheet, Candidates, Combination, 1, 1, NumCandidates, NumCombination
End Sub

Sub CombinationsRecursive(OutputSheet As Worksheet, Candidates() As Integer, Combination() As Integer, CurrentIndex As Integer, StartIndex As Integer, NumCandidates As Integer, NumCombination As Integer)
    Dim i As Integer
    Dim OutputRow As Long
  
    If CurrentIndex > NumCombination Then
        OutputRow = OutputSheet.Cells(OutputSheet.Rows.Count, 1).End(xlUp).Row + 1
        For i = 1 To NumCombination
            OutputSheet.Cells(OutputRow, i).Value = Combination(i)
        Next i
        Exit Sub
    End If
  
    For i = StartIndex To NumCandidates
        Combination(CurrentIndex) = Candidates(i)
        GenerateCombinationsRecursiveToSheet OutputSheet, Candidates, Combination, CurrentIndex + 1, i + 1, NumCandidates, NumCombination
    Next i
End Sub

 

[anonymouspottamus]

Thank you! This is super helpful.