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

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

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

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