I am trying to work with Word Data, in Excel, whereby I would like to take a
certain situations and generate different permutations. For example,
category is Deposit a check and the action is Deposit a check to a savings
account, or checking account, or MMA, or CD etc.

I am not sure if I need to create a macro or if there is a function I could
use.

Help!!!!

Here's something to play with ..

In Sheet1,

Assume you have this 3 x 4 within A1:B4
(ie 3 "action" items in A1:A3, 4 "acc" items in B1:B4)

Deposit a check to a savings account
Withdraw from a checking account
Pass entries to MMA
CD

Then in another sheet,

Put in any starting cell, say in B2:
=OFFSET(Sheet1!$A$1,INT((ROW(A1)-1)/4),)&"
"&OFFSET(Sheet1!$B$1,MOD(ROW(A1)-1,4),)

Copy B2 down by 12 rows to B13 to return the 12 permutations (3 x 4), viz.:

Deposit a check to a savings account
Deposit a check to a checking account
Deposit a check to MMA
Deposit a check to CD
Withdraw from a savings account
Withdraw from a checking account
Withdraw from MMA
Withdraw from CD
Pass entries to a savings account
Pass entries to a checking account
Pass entries to MMA
Pass entries to CD

Adjust the number "4" within both the INT and MOD to suit the number of
items in col B. Then copy the formula down by the number of rows sufficient
to exhaust all the permutations. For eg if you have a 5 x 4 source in Sheet1,
ie 5 items in A1:A5, 4 items in B1:B4, then just use the same formula (no
change required as the number of items in col B is the same), and copy down
by 20 rows (5 x 4) to generate the permutations.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Mr. X." wrote:
I am trying to work with Word Data, in Excel, whereby I would like to take a
certain situations and generate different permutations. For example,
category is Deposit a check and the action is Deposit a check to a savings
account, or checking account, or MMA, or CD etc.

I am not sure if I need to create a macro or if there is a function I could
use.

Help!!!!

Thank you very much Max. It worked great.

What if you wanted to add additional columns? What part of the formula do I
need to change.

Thanks again.

"Max" wrote:

Here's something to play with ..

In Sheet1,

Assume you have this 3 x 4 within A1:B4
(ie 3 "action" items in A1:A3, 4 "acc" items in B1:B4)

Deposit a check to a savings account
Withdraw from a checking account
Pass entries to MMA
CD

Then in another sheet,

Put in any starting cell, say in B2:
=OFFSET(Sheet1!$A$1,INT((ROW(A1)-1)/4),)&"
"&OFFSET(Sheet1!$B$1,MOD(ROW(A1)-1,4),)

Copy B2 down by 12 rows to B13 to return the 12 permutations (3 x 4), viz.:

Deposit a check to a savings account
Deposit a check to a checking account
Deposit a check to MMA
Deposit a check to CD
Withdraw from a savings account
Withdraw from a checking account
Withdraw from MMA
Withdraw from CD
Pass entries to a savings account
Pass entries to a checking account
Pass entries to MMA
Pass entries to CD

Adjust the number "4" within both the INT and MOD to suit the number of
items in col B. Then copy the formula down by the number of rows sufficient
to exhaust all the permutations. For eg if you have a 5 x 4 source in Sheet1,
ie 5 items in A1:A5, 4 items in B1:B4, then just use the same formula (no
change required as the number of items in col B is the same), and copy down
by 20 rows (5 x 4) to generate the permutations.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Mr. X." wrote:
I am trying to work with Word Data, in Excel, whereby I would like to take a
certain situations and generate different permutations. For example,
category is Deposit a check and the action is Deposit a check to a savings
account, or checking account, or MMA, or CD etc.

I am not sure if I need to create a macro or if there is a function I could
use.

Help!!!!

Just "collapse" it 2 cols at a go starting from the rightmost cols, until
you are left with a final single col (or until you run out of rows to
complete the final copy down, ie hitting xl2003 or earlier's limit of 65536,
whichever comes earlier <g).

Here's an example:

Suppose we have a 2 x 5 x 3 x 4 source which we want to permutate to the
final 120 rows

We could start by placing the rightmost 3 x 4 col items into Sheet1's A1:B4
(this is the example in the earlier response), and then pull the 12
permutations out in Sheet2's B1:B12 by placing in Sheet2's B1:
=OFFSET(Sheet1!$A$1,INT((ROW(A1)-1)/4),)&"
"&OFFSET(Sheet1!$B$1,MOD(ROW(A1)-1,4),)
and copying B1 down by: 3 x 4 = 12 rows to B12. This "collapses" the
rightmost 3 x 4 into a single col in Sheet2's B1:B12.

Then we can paste the 2nd source col's "5" items into Sheet2's A1:A5, and
collapse Sheet2's data accordingly into Sheet3's col B by placing in
Sheet3's B1:
=OFFSET(Sheet2!$A$1,INT((ROW(A1)-1)/12),)&"
"&OFFSET(Sheet2!$B$1,MOD(ROW(A1)-1,12),)
then copy B1 down by: 5 x 12 = 60 rows to B60
(adjust the orig. formula to point to Sheet2 as the new source, change the
number within the INT and MOD to suit the # of items in Sheet2's col B, ie
12 items)

Finally, we paste the first source col's "2" items into Sheet3's A1:A2, and
collapse Sheet3's data into Sheet4's col B.

We place in Sheet4's B1:
=OFFSET(Sheet3!$A$1,INT((ROW(A1)-1)/60),)&"
"&OFFSET(Sheet3!$B$1,MOD(ROW(A1)-1,60),)
then copy B1 down by: 2 x 60 = 120 rows to B120
(similarly adjust the formula to point to Sheet3 as the new source, change
the number within the INT and MOD to suit the # of items in Sheet3's col B,
ie 60 items).

Sheet4's B1:B120 will return the final permutated results
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Mr. X." wrote in message
...
Thank you very much Max. It worked great.

What if you wanted to add additional columns? What part of the formula do
I
need to change.

Thanks again.