Get All Combinations From Array Excel VBA Macro

In this post we see how to get all possible combinations of two elements from an array of n total elements with Excel VBA. When order matters (and without repetition), these are called permutations. If values are in the worksheet, we can easily put the range into an array as already explained in this other post: Range Data Into Array. In such case, we can use a worksheet function to get the number of combinations of two elements (or more) from a list. We could also use worksheet functions to get all possible combinations, but it is easier to have a macro do it. This can be used to create a schedule of football matches in Excel as explained in this other post: Sports Fixtures Generator.

Macro/VBA code:

  Sub GetCombinations()

      Dim col As New Collection, arr As Variant
      Dim elem1 As Variant, elem2 As Variant, pair As String
   
      arr = Application.Transpose(Selection)  'creates array from selected range
 
      For Each elem1 In arr
          For Each elem2 In arr
              If elem1 <> elem2 Then  'without repetition
                  pair = elem1 & "-" & elem2

                  Debug.Print pair  'print to immediate window
                  col.Add pair
              End If
          Next elem2
      Next elem1

  End Sub

Macro explained:

• We declare the array variable and the variables that store each element in the array as a Variant. We define a collection that stores each of the pair combinations. We also declare a String variable (pair) that stores the text for each pair. 
• We put selected values from a range into a 1D-array using Application.Transpose. This is all explained in detail in this other post: Range Data Into Array. We could also hard-code values into the array variable using the Array VBA function (e.g. arr = Array("A", "B", "C", "D") for the example above)
•  Then we have a double loop through array elements so that pair combinations can be identified (order matters and without repetition). Such combinations are actually called permutations in mathematics. We can get the number of permutation of two from a total of n elements in Excel using the worksheet function "=PERMUT(4,2)", which is 12 in the example above. We can get the number of combinations of two elements when order does not matter with "=COMBIN(4,2)" with no repetition or "=COMBINA(4,2)" when repetition is allowed (6 and 10 respectively for the example above). 
• Each pair of different elements is printed to the immediate window and added to a collection that can be used later to print or select elements. We could also add it to another array or a comma delimited list. The collection allows to easily acces those elements later, add more or remove elements. We can loop through elements in the collection as shown below.

  Dim i As Integer

  For i = 1 To col.Count
      Debug.Print col.Item(i)
  Next i

• We can use the macro to get combinations of matches for a list of football teams. These are called fixtures when put into a well-organized schedule that determines when teams play each other. That requires a more complex macro and is explained in this other post: Sports Fixtures Generator

This is how we get all combinations from an array in Excel with VBA macros.

Other examples:

• How to Add Drop-Down List To Selection with Excel VBA macros
• How to Add Checkboxes To Selection with Excel VBA macros
• How to Download Email Attachments with Excel VBA macros