How do I get all possible combinations from a list of 41 names on excel?

Question

I have a list of 41 names set up in 5 rows (B4, B5, B6, B7, B8) with 9 columns of names in each row (B4:J8).

How do I get all the possible combinations from this list?

Answer 1

Here's a quick VBA routine that will do this for you. It's pretty simple, and the comments should clue you in on what it's doing (assuming Sheet1 has the data, and Sheet2 is where we are writing combinations)

Sub allnames()
    Dim myrange As Range
    Dim myRow As Integer
    Dim myCell1 As Range, mycell2 As Range

    Set myrange = sheet1.Range("B4:J8") '<--range with your names

    myRow = 1 '<--starting row for writing the combinations in sheet2

    'loop through each cell in the range
    For Each myCell1 In myrange
        'and loop through again to get each combination
        For Each mycell2 In myrange
            'write out the combination
            Sheet2.Cells(myRow, 1).Value = myCell1 & " " & mycell2
            'move to the next row to write in
            myRow = myRow + 1
        Next
    Next
End Sub