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:

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

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.

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.

LikeLike

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.

LikeLike

I dont understand how that can work for me. To clarify i need to have L1 L2 L3 L4……L17 each has under it 1 , x , 2 . If i split i wont get the full amount of combinations surely

LikeLike

Split only the first list in half. It will work for sure.

LikeLike

Pingback: sort but not at random.

Pingback: Huge Combination Conudrum - Please HELP

Really amazing job..thank you very much!

LikeLiked by 1 person

You are most welcome!

LikeLike

Very well done Ejaz….

LikeLiked by 1 person

Thank you!

LikeLike

You are always welcome. Let me know if you have any ideas to make this better.

LikeLike

Hi Himanshu.

This is not a forum. Use the comments section only to comment on the post. If you have questions not pertaining to the post, please send a mail to strugglingtoexcel@outlook.com

Regards,

Ejaz Ahmed

LikeLike

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.

LikeLiked 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.

LikeLike

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!

LikeLiked 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

LikeLike

I used the link in the Comments section. I tried both at work, but evidently the US Govt doesn’t like DropBox 🙂 I’ll give the link above a try.

Thanks again, Ejaz

LikeLiked by 1 person

Let me know if my model solved your problem; if you got to download it first.

Do let me know if you need me to mail it to you.

LikeLike

Ejaz, can I contact you via email? I have a few other questions that would probably be better handled offline. Thanks.

LikeLiked by 1 person

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.

LikeLike

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

very needful

LikeLiked by 1 person

I am glad you found it useful Kaushlendra. And I also hope you would check out a few other posts in my blog. Thanks for stopping by.

LikeLiked by 1 person

Any time! My email is strugglingtoexcel@outlook.com

LikeLike

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.

LikeLiked 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 🙂

LikeLike

Ji… Amazing… No words…

LikeLiked by 1 person

Thanks a lot Ji.

LikeLike

Ejaz, great work

Believe another mrexcel/Chandoo making

LikeLiked by 1 person

I cant even begin to explain how much that means to me. Thank you Sajjad Bhai!

LikeLike