Mix And Match – Get all possible permutations of an unlimited number of lists in Excel


This post is a little fun, not much of a Struggle I guess. In excel, we use permutations more often than we realize. Say you have like twenty performance metrics and you’d like to monitor them for each month, imagine how much copy pasting and concatenating would need being done to get your range names in place?

However, the reason I actually looked into permutation, was for a quirky solution to automating a complex filter problem. As you may already know, advanced-filters has this amazing feature called criteria range. You can create a table with the column headers that you’d like to apply filters to, and add a number of criteria in the rows below. The criteria specified in a row are combined using the AND operator, and then the rows are joined using the OR operator. Unfortunately, I needed it to work the other way around: OR operator within the column and the AND operator between columns.

I needed to filter out a subset of products, sold at a subset of states, delivered to subset of localities. The first idea I got was to write a task specific macro to repeatedly add criteria to a filter, and later copy paste visible cells to another sheet. Where is the fun in that?, So I thought of something different. If I come up with all possible permutations of the criteria, I could stick to excel’s advanced-filters logical structure. I suspect this will wildly increase the time excel takes to execute the filter, but hey mixing and matching is fun!

I hunted  for a solution in the world-wide web and found a few. Most of them involved a bunch of for-loops specific to the number of lists in question; there was this one with a convoluted formula that works for two lists only; and finally, there was one with a sql-based-cartesian-product, which I did not quite understand. Hence, I thought I’d give permutations a shot myself.

The primary aim was that the code should able to handle any number of lists. The solution I came up with is little unconventional, but very intuitive. I will try to explain it what I did. Say we have three sets:

  1. L1 = {1,2}
  2. L2 = {1,2,3}
  3. L3 = {1,2}

Mix N Match

Each element/item repeats itself (red boxes), to form a pattern; and that pattern repeats itself (blue boxes) to complete a column. For instance, in the second column, each element features twice, and that entire pattern (marked by the blue boxes) repeats twice.

The elements: do not repeat in the last column; they repeat X times in the column before that, where X is the number of elements in the last column; and they repeat Y times in the column before that, where Y is the product of the number of elements in the succeeding columns, and so on.The size of a pattern (blue box) is equal to the product of number of elements in the list and the number of times each element repeats itself (red box). The number of times the pattern repeats itself in each column is obtained by dividing the number of permutation possible by pattern size.

Now we calculate, number of permutations possible (number of rows) by multiplying the number of elements in each list together. Then we calculate how many times each element repeats in a pattern, and how many times the patterns repeats itself for each of the columns. Finally, run a bunch of for loops to populate an array with the results.

I coded a generic sub so you could use it in your projects, for advanced filters.

'======================================================================
'MixMatchColumns
'======================================================================
'Macro that accepts a Data Range. Treats each of the columns as a
'set and generates a list of all permutations of the elements in
'each of the lists.
'Arguments:
'DataRange          - Range that contains the elements in each list
'ResultRange        - Cell where the results will be pasted
'DataHasHeaders     - Boolean variable that is used to specify if the
'                     data range included the column headers.
'                     Comes in handy if the CurrentRegion property
'                     is used to select the datarange
'HeadersInResult    - Boolean variable to decide if the uset wants
'                      to paste the headers also along with the results
'======================================================================
'Author     :   Ejaz Ahmed
'Date       :   21 February 2014
'Website    :   https://strugglingtoexcel.wordpress.com/
'Email      :   StrugglingToExcel@outlook.com
'======================================================================
Sub MixMatchColumns(ByRef DataRange As Range, _
                    ByRef ResultRange As Range, _
                    Optional ByVal DataHasHeaders As Boolean = False, _
                    Optional ByVal HeadersInResult As Boolean = False)

Dim rngData As Range
Dim rngResults As Range
Dim lngCount As Long
Dim lngCol As Long
Dim lngNumberRows As Long
Dim ItemCount() As Long
Dim RepeatCount() As Long
Dim PatternCount() As Long
'Long Variables for the Variour For Loops
Dim lngForRow As Long
Dim lngForPattern As Long
Dim lngForItem As Long
Dim lngForRept As Long
'Temporary Arrays used to store the Data and Results
Dim DataArray() As Variant
Dim ResultArray() As Variant

'If the Data range has headers, adjust the
'Range to contain only data
Set rngData = DataRange
If DataHasHeaders Then
    Set rngData = rngData.Offset(1).Resize(rngData.Rows.Count - 1)
End If

'Initialize the Data Array
DataArray = rngData.Value
'Get the number of Columns
lngCol = rngData.Columns.Count

'Initialize the Arrays
ReDim ItemCount(1 To lngCol)
ReDim RepeatCount(1 To lngCol)
ReDim PatternCount(1 To lngCol)

'Get the number of items in each column
For lngCount = 1 To lngCol
    ItemCount(lngCount) = _
        Application.WorksheetFunction.CountA(rngData.Columns(lngCount))
    If ItemCount(lngCount) = 0 Then
        MsgBox "Column " & lngCount & " does not have any items in it."
        Exit Sub
    End If
Next

'Calculate the number of Permutations
lngNumberRows = Application.Product(ItemCount)
'Initialize the Results array
ReDim ResultArray(1 To lngNumberRows, 1 To lngCol)

'Get the number of times each of the items repeate
RepeatCount(lngCol) = 1
For lngCount = (lngCol - 1) To 1 Step -1
    RepeatCount(lngCount) = ItemCount(lngCount + 1) * _
                                RepeatCount(lngCount + 1)
Next lngCount

'Get howmany times the pattern repeates
For lngCount = 1 To lngCol
    PatternCount(lngCount) = lngNumberRows / _
            (ItemCount(lngCount) * RepeatCount(lngCount))
Next

'The Loop begins here, Goes through each column
For lngCount = 1 To lngCol
'Reset the row number for each column iteration
lngForRow = 1
    'Start the Pattern
    For lngForPattern = 1 To PatternCount(lngCount)
        'Loop through each item
        For lngForItem = 1 To ItemCount(lngCount)
            'Repeate the item
            For lngForRept = 1 To RepeatCount(lngCount)
                'Store the value in the array
                ResultArray(lngForRow, lngCount) = _
                        DataArray(lngForItem, lngCount)
                'Increment the Row number
                lngForRow = lngForRow + 1
            Next lngForRept
        Next lngForItem
    Next lngForPattern
Next lngCount

'Output the results
Set rngResults = ResultRange(1, 1).Resize(lngNumberRows, lngCol)
'If the user wants headers in the results
If DataHasHeaders And HeadersInResult Then
    rngResults.Rows(1).Value = DataRange.Rows(1).Value
    Set rngResults = rngResults.Offset(1)
End If
rngResults.Value = ResultArray()

End Sub

I also wrote a simple cover procedure to illustrate how the above macro could be used. Download the workbook to check it out. When you click the ‘Mix And Match’ button in the workbook, you’d be prompted to select the range that contains the data, then you’d have to choose if the headers were included in the data; then you need to point to a cell where the results should be pasted, and finally choose whether you’d like the headers in the results. You could perhaps consider designing a tiny userform instead of this cover macro, for your add-in.

Download MixNMatch V1.01.xlsm from Dropbox. I understand that there may be faster, better ways generate permutations, do share your ideas with me.

Advertisements

30 thoughts on “Mix And Match – Get all possible permutations of an unlimited number of lists in Excel

  1. Amazing sheet I love, loved and will continue to love your work 🙂 I searched for 4 days to find something like this 🙂
    Im having an issue though i have results of 1 x and 2. I can calculate up to 12 combos (531441 combinations) but when i try to go to 13 the page loads for a while then stops working and nothing happens, i assume its too long for excel. I want to go all the way to 17 combos ~130,000,000 results. Any advice would be much appreciated.

    Like

    • There is a solution in the comments section if I remember correctly. Just split one of the lists into two, and run this separately. Combine it later.

      Like

  2. Pingback: sort but not at random.

  3. Pingback: Huge Combination Conudrum - Please HELP

  4. Hi,I liked your work.But i am having a hiccup.The combination i have exceeds the number of cells in excel.is there by any way to write a macros which can add sheets or place the balace data in the next available sheet.

    Liked by 1 person

    • Hi Ramesh,

      I am glad that you loved it.

      First off, if excel cannot handle the data in one sheet, perhaps you should consider using relational databases. Just a thought.

      If you still think having the data in two sheets works for you, there is a quick and dirty hack. You don’t have to tweak the macro.

      Just split the fire list into two. Do this twice, with the first half of the first list, and all of the other lists. Then repeat it with the next. Split the first column into how many ever sub lists to make the results fit into one sheet.

      See if this works. I did not test it out explicitly. Just something I hypothesized. Let me know what happens.

      Also, I hope you visit other posts, and say I love your work next time, instead of loved 🙂

      Regards,
      Ejaz Ahmed.

      Like

  5. This looks like exactly what I need; thanks so much!!! I’m curious what the limit is before it might crash; I currently am looking at 3.42*10^8 permutations.

    I’ve tried putting the code into an Excel module, but it doesn’t show up as an executable macro (I’m a bit of a noob) and I can’t seem to download it from DropBox (from work or home). Any assistance you’d be willing to provide would be most welcome.

    Thanks again. I love your work!

    Liked by 1 person

    • Hi Doctor!

      I am glad you love my work. It feels great to hear positive feedback from my readers.

      The code published in the post accepts arguments, so you can control/use it in your code; and that is why you did not see it in the Macro list. If you just want a one time list, you can use the workbook I speak of in my post. The workbook will also help you to understand how to pass arguments to the Procedure.

      As for why you were not able to download the workbook: Dropbox suffered a security breach recently, and the links I posted initially were rendered no good. I updated them all, but I may have left a few.

      I suspect, you clicked the link in one of the comments; if not tell me which link you clicked, so I can set it right. Try clicking the link at the bottom of the post.

      If that doesn’t work, try this:
      https://www.dropbox.com/s/3mo4xgetra7xd67/MixNMatch%20V1.01.xlsm?dl=0

      It that fails too, give me an email id I can write to; I’d be happy to send it over.

      Regards,
      Ejaz Ahmed

      Like

    • I have used the Long Data type, it should Ideally be able to handle as many rows excel can handle.

      If the code fails, and If you are running a 64 bit version of excel, try changing all the long variables to LongLong datatype.

      Let me know how things workout. I may have to add this tip to my post, if it works.

      Thanks for the support.

      Like

  6. Pingback: [excel] Here’s how to count number of same colored cells | A Project Manager

  7. Ji, this looks like exactly something that i need but i cannot download your workbook from dropbox. it says that the file has been deleted or moved. Can you please provide the document. I would greatly appreciated.

    Liked by 1 person

    • Hi Gil, I am sorry about that, I recently tidied up my folders and sifted them around a little. I did not expect Dropbox to have linked those files to exact file locations on my local hard drive. I have updated the links in the post now.

      https://www.dropbox.com/s/3mo4xgetra7xd67/MixNMatch%20V1.01.xlsm?dl=0

      Thanks for letting me know. I now have to update links on all my posts. While I am at it, I will try to subscribe to a better file hosting solution, one that is not tied up to my local hard drive.

      And, you may also copy paste the code from the post into your project 🙂

      Like

If you liked it, let me know. If you didn't make sure you let me know!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s