Excel 2007 : best method for combinatorial concatenation template?

What's the best way to permutationally concatenate the information in multiple tabs on a spreadsheet, particularly if you don't know in advance how many rows you'll have in each tab?

Let's say you want to combine every populated row in tab 1 with every populated row in tab 2 with every populated row in tab 3. But you want this to work as a template, that can accommodate different data each day, and a possibly different number of rows in each tab from day to day. The data in each tab will be pasted in from a different source, where the number of items may vary from day to day, so you want a clean template that will figure out how many combinations there should be, and create all of them for you in a fourth tab.

So if on a given day tab 1 has three rows:
G
H
I

and tab 2 has six rows:
2
4
6
8
10
12

and tab 3 has 2 rows:
xx
yy

and you wanted to concatenate all these items separated by say ":", you'd end up with output rows in the fourth tab like:

G:2:xx
G:2:yy
G:4:xx
G:4:yy
G:6:xx
G:6:yy
.
.
.
and so forth up to
I:12:xx
I:12:yy

but you want a formula that can automatically or semi-automatically handle days where, for example, tab 1 has 10 rows, tab 2 has 2 rows, and tab 3 has 5 rows, just by looking for the point where the data ends in each tab (where the empty rows begin).

It would be ok if you have to figure out the total # of daily permutations and copy the formula down manually to accommodate the right number of output rows - that is not a difficult daily task to handle...but the less manual and room for error, the better.

Originally Posted by JBeaucaire

Here's a sample workbook, you can just use this to drop your data into, then click the button on Sheet4.

Cool, this works great!

I also figured out how to customize your code to reference different tabs (changing the "Sheet1" etc. references to their actual names); and accommodate items generated by formulas instead of pasted-in constants (change the SpecialCells reference from xlCellTypeConstants to xlCellTypeFormulas).

Terrific!

hmmm, I can't edit the original message, and the followup message does have an edit button, but no "PREFIX" box. ?