You could use a VBA thingy but you can do without it.
Suppose you have your list in A:D starting with the headers in row 1.
Then, define the following named formulas (Insert | Name Define...).
While strictly not necessary, they make life a lot easier. Not to
mention enable you to modify your 4 lists and have the solution
automatically include the new entries.
NbrSoftware =COUNTA(Sheet2!$A:$A)-1
NbrConfig =COUNTA(Sheet2!$B:$B)-1
NbrAction =COUNTA(Sheet2!$C:$C)-1
NbrTest =COUNTA(Sheet2!$D:$D)-1
Now, in H2 enter the formula below. It gives you a repeating pattern
for the 'test' list:
=OFFSET($D$2,MOD(ROW()-ROW($D$2),NbrTest),0,1,1)
In G2 enter the formula for a repeating pattern for the 'action':
=OFFSET($C$2,INT(MOD(ROW()-ROW($C$2),NbrTest*NbrAction)/NbrTest),0,1,1)
Given G2 you should be able to guess the formulas for F2 and E2.
F2:
=OFFSET($B$2,INT(MOD(ROW()-ROW($B$2),NbrTest*NbrAction*NbrConfig)/
(NbrTest*NbrAction)),0,1,1)
and E2:
=OFFSET($A$2,INT(MOD(ROW()-ROW($A
$2),NbrTest*NbrAction*NbrConfig*NbrSoftware)/
(NbrTest*NbrAction*NbrConfig)),0,1,1)
Copy E2:H2 as far down as needed. The total number of entries will be
NbrTest*NbrAction*NbrConfig*NbrSoftware
--
Regards,
Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
In article , "Lucy Lastic"
<Lucy Lastic@Lucy Lastic.com says...
Hi,
I have a little job to do which involves testing different software,
harvesting the results and getting the data displayed in a pivot table.
There are 4 softwares,
For each softtware there are 2 different configs,
For each config there are 3 different actions,
For each action there are 3 different tests.
I can handle setting up the pivot table from the data, but I need to get the elements of the table
and all possible permutations into a list for data validation so the tester would then just have to choose a test from a drop down validation list and then key in the result for that test.
That list would be the base of a pivot table.
(This kind of problem rears it head in a variety of situations and it has always made ma wonder why Excel is so good a making pivot tables from lists and - apparently - useless at making lists from tables.)
Is there some smart way of getting XL to make a list of permutations from a table of elements ? or would I have to make a VBA thing that would produce a list using nested loops ?
This is what I have :
Elements
Software Config Action Test
TTT alpha print a
AAA beta visu b
OOO delete c
ZZZ
This is what I need :
List
Test
TTT alpha print a
TTT alpha print b
TTT alpha print c
TTT alpha visu a
TTT alpha visu b
TTT alpha visu c
TTT alpha delete a
TTT alpha delete b
TTT alpha delete c
TTT beta print a
TTT beta print b
TTT beta print c
TTT beta visu a
TTT beta visu b
TTT beta visu c
TTT beta delete a
TTT beta delete b
TTT beta delete c
AAA alpha print a
AAA alpha print b
AAA alpha print c
AAA alpha visu a
AAA alpha visu b
AAA alpha visu c
AAA alpha delete a
AAA alpha delete b
AAA alpha delete c
AAA beta print a
AAA beta print b
AAA beta print c
AAA beta visu a
AAA beta visu b
AAA beta visu c
AAA beta delete a
AAA beta delete b
AAA beta delete c
etc...
TIA
Lucy