Make a list from a table of elements ?

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

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 <[email protected]>, "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
>
>
>