Creating Entries Automatically from Variables

[zimbound]

Hi,

I have the task of creating individual entries in Excel for a bunch o
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 o
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 possibl
combinations into individual rows? I've started by doing a lot o
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 secon
method you suggested now.

You're a pro!

Dav