Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Creating Entries Automatically from Variables
Hi, I have the task of creating individual entries in Excel for a bunch of different variables. For example, the variables could be: Color (Green, Red, Blue, Yellow) Size (1', 2', 3', 4', 5') Quantity (10, 20, 30, 40, 50) Now, I would need to create an entry for each possible combination of these variable. Example: Green, 1', 10 Green, 1', 20 Green, 1', 30 and so on... Is there a way in Excel or Access to auto-generate all the possible combinations into individual rows? I've started by doing a lot of copying and pasting, but this method seems too time consuming. Any ideas??! Thanks! Dave B. -- zimbound ------------------------------------------------------------------------ zimbound's Profile: http://www.excelforum.com/member.php...o&userid=36597 View this thread: http://www.excelforum.com/showthread...hreadid=563398 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Creating Entries Automatically from Variables
zimbound Wrote: Hi, I have the task of creating individual entries in Excel for a bunch of different variables. For example, the variables could be: Color (Green, Red, Blue, Yellow) Size (1', 2', 3', 4', 5') Quantity (10, 20, 30, 40, 50) Now, I would need to create an entry for each possible combination of these variable. Example: Green, 1', 10 Green, 1', 20 Green, 1', 30 and so on... Is there a way in Excel or Access to auto-generate all the possible combinations into individual rows? I've started by doing a lot of copying and pasting, but this method seems too time consuming. Any ideas??! Thanks! Dave B. One way to do this is to map each of these values to a number from 0 to the number of items in the list. For example: Color (0-3) 4 possibilities 0 - Green 1 - Red 2 - Blue 3 - Yellow Size (0-4) 5 possibilities Quantity (0-4) 5 possibilities I'll assume Color in Column A, Size in Column B, Quantity in Column C. In A1 (and drag down 200 rows), put the formula =MOD(INT(ROW(A1)/25),4) In B1 (and drag down 200 rows), put the formula =MOD(INT(ROW(A1)/5),5) In C1 (and drag down 200 rows), put the formula =MOD(ROW(A1),5) (You've done as much as you need to do when each column has a 0 in it, should happen at row 200 for these values.) This will give you three numbers on each row which refer to our mappings above. Basically, you just need to substitute the values in for the numbers. There are many ways to do this. If you need help with it, or more of an explanation of how this works, just reply back. Scott -- Maistrye ------------------------------------------------------------------------ Maistrye's Profile: http://www.excelforum.com/member.php...o&userid=36078 View this thread: http://www.excelforum.com/showthread...hreadid=563398 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Creating Entries Automatically from Variables
zimbound Wrote: Hi, I have the task of creating individual entries in Excel for a bunch of different variables. For example, the variables could be: Color (Green, Red, Blue, Yellow) Size (1', 2', 3', 4', 5') Quantity (10, 20, 30, 40, 50) Now, I would need to create an entry for each possible combination of these variable. Example: Green, 1', 10 Green, 1', 20 Green, 1', 30 and so on... Is there a way in Excel or Access to auto-generate all the possible combinations into individual rows? I've started by doing a lot of copying and pasting, but this method seems too time consuming. Any ideas??! Thanks! Dave B. Haha, was thinking, and there is a slightly better way: A1: = CHOOSE(MOD(INT((ROW(A1)-1)/25),4)+1,"Green","Red","Blue","Yellow") A2: = CHOOSE(MOD(INT((ROW(A1)-1)/5),5)+1,"1'", "2'","3'","4'","5'") A3: = CHOOSE(MOD((ROW(A1)-1),5)+1,10,20,30,40,50) The ROW(A1)-1 just makes it so it starts at 0... the pattern looks better that way. Scott -- Maistrye ------------------------------------------------------------------------ Maistrye's Profile: http://www.excelforum.com/member.php...o&userid=36078 View this thread: http://www.excelforum.com/showthread...hreadid=563398 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Creating Entries Automatically from Variables
Thanks, Scott! I appreciate the help. I fooling around with the second method you suggested now. You're a pro! Dave -- zimbound ------------------------------------------------------------------------ zimbound's Profile: http://www.excelforum.com/member.php...o&userid=36597 View this thread: http://www.excelforum.com/showthread...hreadid=563398 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
tagging unique items in a list | Excel Worksheet Functions | |||
Getting remaining entries | Excel Discussion (Misc queries) | |||
Table to pick out most common entries and count occurences of each | Excel Worksheet Functions | |||
Creating combo box with different number of variables | Excel Worksheet Functions | |||
How do I change multi-line entries to single line entries in Exce. | Excel Worksheet Functions |