Create a matrix of data automatically

[Nathalie Sicard]

Hello,
I have a problem that I need to get solved to make my work more efficient and I'd like you to help me, I'll appreciate it very much.

I have 2 types of data: Product & Store

And the data would go like this:

A
Corn Flakes
Frosted Flakes
Froot Loops

B
Store 1
Store 2
Store 3
Store 4

The number of entries of course is a lot bigger, that's why I'm trying to make it auto.

So what I need is to have a table created that would mix both data type in 2 columns, like this:

Product	Store
Corn Flakes	Store 1
Corn Flakes	Store 2
Corn Flakes	Store 3
Corn Flakes	Store 4
Frosted Flakes	Store 1
Frosted Flakes	Store 2
Frosted Flakes	Store 3
Frosted Flakes	Store 4
Froot Loops	Store 1
Froot Loops	Store 2
Froot Loops	Store 3
Froot Loops	Store 4

<tbody>
</tbody>

How would this be done automatically by a formula?

Thank you very much!!!

 

[sheetspread]

Excel 2010
	A	B
1	Corn Flakes
2	Frosted Flakes
3	Froot Loops
4
5
6	Corn Flakes	Store 1
7	Corn Flakes	Store 2
8	Corn Flakes	Store 3
9	Corn Flakes	Store 4
10	Frosted Flakes	Store 1
11	Frosted Flakes	Store 2
12	Frosted Flakes	Store 3
13	Frosted Flakes	Store 4
14	Froot Loops	Store 1
15	Froot Loops	Store 2
16	Froot Loops	Store 3
17	Froot Loops	Store 4
Sheet7

Cell Formulas
Range	Formula
A6	=OFFSET($A$1,ROUNDUP(ROW(A1)/4,0)-1,0)
B6	="Store "&MOD(ROW(A1)-1,4)+1

 

[Nathalie Sicard]

Thank you very much for your quick response! There's only one thing.

The "stores" are not really named "Store 1". They have actual names that I can't predict through a formula, e.g. "ECATEPEC"

How would it be done with this slight change?

Thank you!!!

 

[sheetspread]

Like this?

Excel 2010
	A	B
1	Corn Flakes	Store 1
2	Frosted Flakes	Store 2
3	Froot Loops	Store 3
4		Store 4
5
6	Corn Flakes	Store 1
7	Corn Flakes	Store 2
8	Corn Flakes	Store 3
9	Corn Flakes	Store 4
10	Frosted Flakes	Store 1
11	Frosted Flakes	Store 2
12	Frosted Flakes	Store 3
13	Frosted Flakes	Store 4
14	Froot Loops	Store 1
15	Froot Loops	Store 2
16	Froot Loops	Store 3
17	Froot Loops	Store 4
Sheet7

Cell Formulas
Range	Formula
A6	=OFFSET($A$1,ROUNDUP(ROW(A1)/COUNTA($B$1:$B$4),0)-1,0)
B6	=OFFSET($B$1,MOD(ROW(A1)-1,COUNTA($B$1:$B$4)),0)

 

[Nathalie Sicard]

I just applied the formula, but I am having an issue with the first product. For some reason, it only gets repeated 2 times (I need it repeated 6 times / 6 stores). Other than that, it works. Any idea of what could it be?

Thanks!

 

[sheetspread]

$A$1 and $B$1 refer to the first cell in the product and store lists, change these if the data begins somewhere else. Row(A1) in the formulas acts as a counter starting from 1 so be sure to keep that the same wherever the formula cells begin.

 

[Nathalie Sicard]

Yeah, I checked.

My data starts in A5. and the other data is in F5. (The actual data, no header)

My formulas go like this:
=OFFSET($A$5,ROUNDUP(ROW(A5)/COUNTA($F$5:$F$10),0)-1,0)
=OFFSET($F$5,MOD(ROW(A5)-1,COUNTA($F$5:$F$10)),0)

Can you see something wrong?

THANK YOU REALLY!!!

 

[sheetspread]

So change:

=OFFSET($A$5,ROUNDUP(ROW(A5)/COUNTA($F$5:$F$10),0)-1,0)
=OFFSET($F$5,MOD(ROW(A5)-1,COUNTA($F$5:$F$10)),0)

To:

=OFFSET($A$5,ROUNDUP(ROW(A1)/COUNTA($F$5:$F$10),0)-1,0)
=OFFSET($F$5,MOD(ROW(A1)-1,COUNTA($F$5:$F$10)),0)

 

[Nathalie Sicard]

OOHHHH! It worked! Perfect!
Why does it have to go A1?

1000 Thank you's!

 

[sheetspread]

Why does it have to go A1?

You're repeating each position in the product list, say 6 times here, and Row(A1) = 1 then increases by 1 each time you drag down. Starting with Row(A5) = 5 will omit the first four repetitions but nothing more. Similarly, when cycling through the stores 1-2-3-4-5-6, you're starting with a 5 but continuing in the same pattern as you would have if correctly done at 1, so only the first 4 will be missing.