Generating Combinations of Text

Hi,

I am trying to find a way to generate all possible combinations from a list. The requirements of this specific task differ from other posts I've read.

1. Each combination should maintain the order of the rows and thus eliminate duplicates caused by switching fields. ex: Column A: Dog Column B: fur = Dog,fur NOT fur,dog

2. This is text based as each column represents a parameter for systems testing and thus each column must be included in each result. => every result should have 8 variables generated.

3. Currently I have 8 columns each with a different number of variables (<5 in each) but i would like to be able to change this in the event we need to add further categories.

Any guidance provided would be greatly appreciated and a huge time saver!
Many Thanks.

Here is a quick & dirty but effective solution. In this example the categories are just numbers 1-8, and the values are just letters of the alphabet, 2-5 different values for each category.

I hope you are going to automate this test because the number of combinations get quite large quickly. It is not hard to add more categories but with Excel 2003 you will be limited to 64K test cases. Note that with my simple example there are over 8K test cases already.

Some Assembly Required
Due to the size, you are going to have to go into the Enumerations and Combinations sheets and copy & fill as instructed. If I had provided a complete solution it would be too big to attach here.

Another approach that gives a cleaner spreadsheet would be using VBA, which would have taken me a little longer to put together.

Let me add that:

To add values to categories, you just need to add the values in the appropriate columns, then to go Enumerations and Combinations to copy the formula to as many rows are needed. In Enumerations you will notice that when all cases are enumerated the "odometer" rolls over to 11111111.

To add categories, you should add new category columns to the left, and then add new columns to those two worksheets and copy the leftmost column formulas into the new columns. Then also extend the rows as described above.

(A VBA solution would have the advantage of doing no additional work to add values or categories.)

A note on capacity
If you have 8 categories with 4 different values each, that will consume the maximum rows available in Excel 2003.

Originally Posted by financeguy57

Thanks Jimmy! Looked at the spreadsheet you gave me and it popped out all the combos that I wanted. I can certainly shave off a ton of these and come up with good sample cases.

All the best and thank you again for your input!

With all due respect to JimmyA, it doesn't look like he posted a file. Did you mean to address that to me?

Another alternative attached.