Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make a list from a table of elements ?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Determining elements that make up sum from a list | Excel Discussion (Misc queries) | |||
list of unique elements | Excel Worksheet Functions | |||
make a list from a table | Excel Discussion (Misc queries) | |||
How to format two repeating XML elements w/o getting list of list | Excel Discussion (Misc queries) | |||
Project Elements as One List | Excel Programming |