permutation - Excel vba to create every possible combination of a Range

Question

I have a problem that I haven't been able to find anywhere on the web (it may be there, but I can't find it, heh).

I have a spreadsheet with 13 columns of data. Each of the column contains variations of a parameter that needs to go into an overall test case.

All of them differ, like

E:
101%
105%
110%
120%

J:
Upper S
Upside L
Downside B
Premium V

I have seen several solutions to the combination issue which uses nested loops. I'd like to steer clear of 13 nested loops (but this is my best bet at the moment). I'm kind of at a loss on how to generate every unique combination in in each column.

I'm not sure if that makes enough sense for you guys. I was hoping someone could at least point me in the right direction with a recursive algorithm. I'd like to make it dynamic enough to take varying numbers of columns and rows.

Thanks for any help you guys can give me.

Answer 1

Since I offered an ODBC approach I thought I should elaborate on it, as it is not immediately obvious how to do this. And, in honesty, I needed to relearn the process and document it for myself.

This is a way to generate a Cartesian product of two or more one-dimensional data arrays using Excel and Microsoft Query.

These instructions were written with XL2007 but should work with minor (if any) modifications in any version.

Step 1

Organize the arrays in columns.

Important: Each column should have two "header" names as shown in bold below. The topmost name will later be interpreted as a "table name". The second name will be interpreted as a "column name". This will become apparent a few steps later.

Select each data range in turn, including both "headers", and hit Ctrl+Shift+F3. Tick only Top row in the 'Create Names" dialog and click OK.

Once all named ranges are established, save the file.

Step 2

Data | Get External Data | From Other Sources | From Microsoft Query

Choose <New Data Source>. In the Choose New Data Source dialog:

1. A friendly name for your connection

2. choose the appropriate Microsoft Excel driver

... then Connect

Step 3

Select Workbook... then browse for your file.

Step 4

Add the "columns" from your "tables". You can see now why the "two header" layout in step 1 is important--it tricks the driver into understanding the data correctly.

Next click Cancel (really!). You might be prompted at this point to "continue editing in Microsoft Query?" (answer Yes), or a complaint that joins cannot be represented in the graphical editor. Ignore this and forge on...

Step 5

Microsoft Query opens, and by default the tables you added will be cross-joined. This will generate a Cartesian product, which is what we want.

Now close MSQuery altogether.

Step 6

You are returned to the worksheet. Almost done, I promise! Tick New worksheet and OK.

Step 7

The cross-joined results are returned.