heiniquadrassel
New Member
- Joined
- Jan 27, 2008
- Messages
- 5
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
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