Creating Entries Automatically from Variables

[zimbound]

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.

[Maistrye]

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]

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

[zimbound]

Thanks, Scott! I appreciate the help. I fooling around with the second method you suggested now.

You're a pro!

Dave