bulk permutation of words

[heiniquadrassel]

Hi all,

i have read through all threads about permutation, and also a few others. I if I were an Excel Pro I would probably have been able to come up with a solution to my problem based on all the suggestions made, but unfortunately it is (still) all a bit too complex for me.

The problem:

I have between 2 and 5 lists of words, each list in one column:

Column A

red
green
yellow
black
blue green
pink

Column B

shirt
jumper
blouse
coat
t-shirt
skirt

Column C

for women
for men
for girls
extra large
for boys
one size fits all

Column D

etc

Note that one cell may contain several words, separated by a space.

I need to permute the cells in a way that gives me the following result:

red shirt for women
red shirt for men
red shirt for girls
red shirt extra large
red shirt for boys
red shirt one size fits all
red jumper for women
red jumper for men
...
pink skirt for boys
pink skirt one size fits all


I do not want to permute the words in a single cell like:

red shirt large extra
red size one shirt fits all


The cells may contain characters like "-", "'" or ",".

The list may be very long, but I expect the resulting list to fit into one Excel 2007 column, although if it would be possible to split up the result list into several columns just in case that would be nice.

It would be great if I could choose everytime I perform this task to permutate either only in this way:


A1&B1&C1
A1&B1&C2
A1&B1&C3
A2&B1&C1
A2&B1&C2
...

or also

A1&B1&C1
B1&C1&A1
B1&A1&C1
C1&A1&B1
...

which would obviously produce many more results and would normally not be necessary.


To put it into a nutshell I think I need a macro that concatenates each cell in column A with each cell in Column B, C, D, and E in either all possible combinations, or in all possible combinations but with keeping the original order of columns, and writes all resulting combinations in one column.



Please excuse me for being unclear but I am not used to expressing such problems in this form.

Any suggestions appreciated.

cheers,
Arian

 

[erik.van.geit]

Refering to Richards post

6. The Query Wizard-Choose Columns dialog will open. Hit the Options button at the bottom which will open up another dialog (Table Options). Make sure "System Tables" is checked in the list>OK

I cannot see anything of the kind, using XP.

my steps
menu data / import external data / import data
chose Excel file
dialog box:
title: "choose table"
labels on top are: Name, Description, Changed, Created, Type
there are 2 sheetnames
bottom: checkbox: "first row has headers" 2 buttons: OK, Cancel

and then

kind regards,
Erik

 

[Richard Schollar]

You want a New database Query Erik

 

[erik.van.geit]

You want a New database Query Erik

Thanks!
Now got stuck in next step: 'this source doesn't contain visible tables'

 

[Richard Schollar]

Have you gone to Options and ensured you have System Tables checked?

 

[erik.van.geit]

Have you gone to Options and ensured you have System Tables checked?

No, thanks for that. It is clear now when rereading your explanation, but struggling with Dutch version etcetera. It's totally new for me...

Now trying to figure out how to modify

SELECT T1.Colour, T2.Item, T3.Type
FROM `C:\Users\Richard\Documents\Spreadsheets\QTTest.xlsx`.`Sheet1$` T1,`C:\Users\Richard\Documents\Spreadsheets\QTTest.xlsx`.`Sheet1$` T2,`C:\Users\Richard\Documents\Spreadsheets\QTTest.xlsx`.`Sheet1$` T3

I wonder where it is defined how to create all combinations.

 

[erik.van.geit]

currently having this in the SQL window

SELECT `Sheet1$`.List1, `Sheet1$`.List2, `Sheet1$`.List3
FROM `C:\Map1`.`Sheet1$` `Sheet1$`

 

[dave3009]

Hi Erik

I copied the address for this from my Windows Explorer then added the file name and SQL dataset T1 etc.

Hope it helps

Dave

 

[erik.van.geit]

thanks
still chinese for me
where does "T1" come from? is that some special syntax to get combinations

 

[Richard Schollar]

What you are going to create is 3 self joins using the SQL, so that each column in the result is from a separate reference to the underlying data (ie we call the data table 3 times and take a different column from each):

SELECT T1.List1, T2.List2, T3.List3
FROM `C:\Map1`.`Sheet1$` T1, `C:\Map1`.`Sheet1$` T2, `C:\Map1`.`Sheet1$` T3

The T1, T2 and T3 bits are just aliases for the table (to differentiate them from each other) - the important bit is after the SELECT where we have specified different aliases for each column. Because we have not applied any restrictions we result in the product being returned ie 6 times 6 times 6 records representing all the combinations possible with T1 first, T2 second, T3 third.

 

[dave3009]

Chinese for me too Erik, I think Richard has been studying hard

SELECT: T1.Colour, T2.Type etc

Look like it's referencing the header of the file you define in C:\

KR


Dave