How can you populate calculation tree easily?

[adamis]

I need to be able to fill in all of the possible calculations from up to 5 columns of information. For example, I have 3 columns with an unknown number of cells populated in each column (in the example below I just limited it to three rows).

1 a !
2 b @
3 c #


I need the macro to produce the following:



1 a !
1 a @
1 a #
1 b !
1 b @
1 b #
1 c !
1 c @
1 c #
2 a !
2 a @
2 a #
2 b !
2 b @
2 b #
2 c !
2 c @
2 c #
3 a !
3 a @
3 a #
3 b !
3 b @
3 b #
3 c !
3 c @
3 c #


I have 5 columns of information that needs to be branched like this so it would take quite a while to do it by hand.

Any suggestions?

 

[Richard Schollar]

Greg

The way I got it to work was saving down an Excel file with one column's data in Sheet1 (eg in rows A2:A10 say, with A1 being a header - such as "Field1") and the second column's data in Sheet2 (A2:A10 or whatever with "Field1" again). This is because of the way MSQuery references data when it is querying Excel files. Save it down, and in another sheet go Data>Get External Data>New Database Query.

This will fire up the MSQuery dialog and you want to select Excel type files, make sure the "Use Wizard" option is unchecked and click OK.

This opens up MSQuery itself - navigate to the Excel file you just saved down and click OK. When it gets to add table, make sure you have System Tables in the Options selected, then you can click to add both Sheet1$ and Sheet2$.

Click the SQL button, and the SQl you need is:

SELECT * FROM Sheet1$,Sheet2$

this returns the cartesian product ie so each record of sheet1$ for example is paired up against all records of sheet2$ ('cos its a Full Join in SQL terminology).

It actually sounds a bit long-winded now I write it down - it is much simpler if the tables (or table) is in Access itself!

Hope this has shed a little light though!

Richard

 

[Greg Truby]

Hi Richard! Thought you might enjoy this.

 

[Richard Schollar]

Greg - Thanks! I am amazed this has proved to be at all useful rather than just an intellectual curiosity!

Richard

 

[tusharm]

He, he! Nice idea!

If you wanted to do it mega-quick with a large number of items and without the use of VBA then you could write a SQL statement and go thru MSQuery to generate the output (it's simply a cartesian product). Or do it in Access or any other database program (especially if you have 250 or so records in each column as you'll exceed Excel's row capacity).

Richard

 

[tusharm]

See Myrna Larson's post at http://groups.google.com/group/micr..._frm/thread/8ac4a84b7df1ff97/5deaecfc5da1db8d

If you are also interested in something other than permutations, see
Powerset, Subset, and Combinations & Permutations
http://www.tushar-mehta.com/excel/tips/powerset.html

I need to be able to fill in all of the possible calculations from up to 5 columns of information. For example, I have 3 columns with an unknown number of cells populated in each column (in the example below I just limited it to three rows).

1 a !
2 b @
3 c #


I need the macro to produce the following:
{snip}
Any suggestions?

 

[Richard Schollar]

I realised I included an unnecessary step in that tables on different sheets aren't required as table aliases can be used to specify different columns from the same sheet. ie:

SELECT a.FieldNumbers, b.FieldText 
FROM `H:\Cartesian.xls`.`Sheet1$` As a,`H:\Cartesian.xls`.`Sheet1$` As b 
WHERE b.FieldText Is Not Null

where FieldNumbers and FieldText are both columns in the same table. The Where clause was inserted because each column had different numbers of rows, and null values were not required.

You also do not need a separate workbook, since you can reference the current one (using the same syntax as above).


Richard