DigDB      Excel Add-ins      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: 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: Excel does NOT support this kind of mix & combine operations. DigDB allows you to do this easily. Use the following sheet as an example:
    Mix & Combine Sets
    Practice file - mix-combine-sets-demo.xls (16k)

  1. Invoke 'DigDB->Mix Sets...'

    Mix & Combine Sets

    A 3-step wizard will start.

    Mix & Combine Sets

  2. Click 'Next', now you can select the sets, select the order, and the filler text before, after, or in-between sets.

    Mix & Combine Sets

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

    Mix & Combine Sets

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

    Mix & Combine Sets

    Add Set1

    Mix & Combine Sets

  5. Iterate the same steps, to add '-', Set2 range, '-', Set3 range, ']' into the combining pool

    Mix & Combine Sets

  6. 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'

    Mix & Combine Sets

  7. The result list of all possible combinations in a new sheet.

    Mix & Combine Sets

Need more?


Features | Testimonial | Download | Purchase | FAQ | Contact

Copyright © 1999-2007 All rights reserved

DigDB      Excel Add-ins      Add-in Tools for Excel Elites
Who else wants to do a 10-hour Excel analysis in 15 minutes?

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 Office Marketplace logo
Microsoft and the Office logo are trademarks or registered trademarks of
Microsoft Corporation in the United States and/or other countries.