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 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 column header 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.
Lets get to the code…
I coded a generic sub so you could use it in your projects, for advanced filters, and get all permutations in general:
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
Download
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.
I understand that there may be faster, better ways generate permutations, do share your ideas with me.
Ejaz, great work
Believe another mrexcel/Chandoo making
I cant even begin to explain how much that means to me. Thank you Sajjad Bhai!
Ji… Amazing… No words…
Thanks a lot Ji.
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.
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 🙂
very needful
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.
Any time! My email is strugglingtoexcel@outlook.com
Pingback: [excel] Here’s how to count number of same colored cells | A Project Manager
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!
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
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
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.
Ejaz, can I contact you via email? I have a few other questions that would probably be better handled offline. Thanks.
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.
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.
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.
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
Really amazing job..thank you very much!
You are most welcome!
Very well done Ejaz….
Thank you!
You are always welcome. Let me know if you have any ideas to make this better.
Pingback: Huge Combination Conudrum - Please HELP
Pingback: sort but not at random.
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.
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.
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
Split only the first list in half. It will work for sure.
Hi Ejaz
Is great the Mix and Match code, is easy to understand and Use, But im Wonder,
it is a way to “filter out combinations” but using check mark box? with a macro or vba?
i googled but with luck.
Example:
if Combination 1,1,1,1,1 of 1
if Combination 1,1,2,1,1 1 and 2
if Combination 3,3,3,1,1 1 and 3
if Combination 3,4,1,1,1 1,3 and 4
if Combination 1,2,3,1,5 1,2,3,5
if Combination 1,2,3,4,5 <- means combination of 5 with 5 digits
Now, the idea of Check box mark (when marked) is to filter out, let say
If Check Mark box 1 [X] is marked then Combination of 1 digit are out
If Check Mark box 2 [X] is marked then Combination of 1 digit are out
If Check Mark box 3 [X] is marked then Combination of 1 digit are out
If Check Mark box 4 [ ] is no marked then Combination of 4 digits stay on the list
If Check Mark box 5 [ ] is no marked then Combination of 5 digits stay on the list
Thanks!!!!!
There are many ways to do it. The reason why you might not have found help in Google is because, no one might have had this specific question. However, excel has a set of generic features that you can use in tandem to achieve what you want. You’ll stumble across answers to this question tomorrow, when you are searching for something else.
Let me try to give you an easy solution. For starters, it would help to concatenate the results into a single cell. Use a delimiter, so you can split it out later.
Then you can use the AND, FIND, ISERROR and NOT functions to write a formula that will return a TRUE for the combination you are looking at, in an adjacent column. Then you can filter TRUEs in that column.
Remember that this is a permutations generator. Not a combinations generator. I have developed a combination generator also. I will write a post on it as soon as I get some time.
Hi Ejaz,
the code make the permutation only when L1,L2,L3,L4,L5 Have different qty of Digit,i.e L1=2 Digit, L2=1 Digit, L3=4 Digits, etc,etc.
But when L1 to L5 have an exact same qty of digit by each L, then it become combination generator.
your help is appreciated!.
Best Regards
Andy
Thank you SO MUCH for creating this workbook! Was searching struggling with this for a long time. Really appreciate you making this public.
Hi there – this macro continues to give, been using for 4 years now. I was wondering if there were new versions that could handle permutating out text items such as: ‘0001
without turning it into a number: 1
Also: ‘2E7
without turning it to: 20000000
I am glad you found this application helpful. You can tweak the formulas that are hidden away to make it sing. Explore the hidden columns. Give it a shot and let me know how it goes.
Hi Ejaz, wondering if you are still able to re-share a copy of the file? Wasn’t able to download from the links indicated above. Tks!
I’ve updated links. Click the down arrow + my logo pic. You should get a OneDrive link.
Thanks much. This was just want I needed to create a list of permutations for the QA team. (after some filtering of course)