Mix & Combine Sets of Cells to List All Possible Combinations

DigDB

Add-in Tools for Excel Elites
Who else wants to do a 10-hour Excel analysis in 15 minutes? (more ...)

Features | Testimonial | Download ^{free 15-day trial} | Purchase | FAQ | Contact

Mix & Combine Sets of Cells to List All Possible Combinations

Suppose we have three sets of values, each set is a range:

Set1 = (X, Y, Z)
Set2 = (a, b, c)
Set3 = (1, 2, 3)

How do I generate a list of all possible combinations of the 3 sets? Suppose we also want the combination to follow the pattern '[' + Set1 + '-' + Set2 + '-' + Set3 + ']'. For example, the result list will be:

Result list = ([X-a-1], [X-a-2], [X-a-3], [X-b-1], [X-b-2] ....) (27 values)

Excel does NOT support this kind of mix & combine operations. DigDB allows you to do this easily. Use the following sheet as an example:

Practice file - mix-combine-sets-demo.xls (16k)

Invoke 'DigDB->Mix Sets...'

A 3-step wizard will start.
Click 'Next', now you can select the sets, select the order, and the filler text before, after, or in-between sets.
User the text input box to input the texts. to get a combined [X-a-1], we enter '[' first, then use the lower 'Add >>' button to add the texts to the combining pool.
Now we need to enter the range of Set1. Click into the range selector, then go to the Set1 area, select the range, the range's address will show in the range selector. Click the upper 'Add >>' to add the range as a set to the combining pool.

Add Set1
Iterate the same steps, to add '-', Set2 range, '-', Set3 range, ']' into the combining pool
Click 'Next' to go to Step3. Now DigDB tells you how many combination values will be in the result list. The result list will output to a new sheet, starting from cell A1, fill out the column, if needed, one after another.
The default is to fill out the maximum of 65536 rows and 256 columns. To change this behavior, for example, you can set the max row to 20, and set the max column to 1. This way, each sheet will have only 1 column, after the first 20 values is outputted, a new sheet will be created to store the rest.
Default is enough for most of time. Just click 'Finish'
The result list of all possible combinations in a new sheet.

Need more?

Browse the complete list of DigDB's powerful features listed on the front page.
If you see errors or things not working, check out our error reference page.

Related keywords: Advanced excel tips, excel questions tools, Excel reporting applications, Excel add-ins add-in, Excel shareware, Excel plugin plug-ins plugins, Excel tricks question, Excel utility utilities, Excel add-on add-ons, Excel tip tips, Power excel technique, Excel manipluation, Excel guru, Excel trick, Excel toolkit, Excel app power user

Microsoft and the Office logo are trademarks or registered trademarks of
Microsoft Corporation in the United States and/or other countries.