Help to build a table

[Mary]

Please help me to build a table similar to this one
The number on the first column will change after 16 times, it will go in
sequence from 1001, 1002, etc…
The number in the second column will change after 4 times and it will go
from 1 to 4
The number in the 3rd column will go from 1 to 4
2nd and 3rd column will keep the same pattern.

Cabinet Shelf Location
1001 1 1
1001 1 2
1001 1 3
1001 1 4
1001 2 1
1001 2 2
1001 2 3
1001 2 4
1001 3 1
1001 3 2
1001 3 3
1001 3 4
1001 4 1
1001 4 2
1001 4 3
1001 4 4
1002 1 1
1002 1 2
1002 1 3
1002 1 4
1002 2 1
1002 2 2
1002 2 3
1002 2 4
1002 3 1
1002 3 2
1002 3 3
1002 3 4
1002 4 1
1002 4 2
1002 4 3
1002 4 4

 

[Luke M]

Here's one way. Assuming your table has headers in row 1.
C2 formula:
=IF(C1=4,1,C1+1)

B2 formula:
=OFFSET($C$2,INT(COUNT($C$1:$C1)/4),)

A2 formula:
=1001+INT(COUNT($C$1:$C1)/16)

Copy down as needed

 

[Dave Peterson]

Start a new worksheet.

Put this in A1:
=INT((ROW()-1)/16)+1+1000

Put this in B1:
=INT((ROW()-1)/4)+1

Put this in C1:
=MOD(ROW()-1,4)+1


Select A1:C1 and drag down as far as you need.

Select columns A:C and copy|paste special|Values

Then copy|paste the range to its real home.

And delete the new worksheet.

 

[Luke M]

Dave,

OP wanted column B to repeat numbers 1-4.

 

[Dave Peterson]

You're right:
=MOD(INT((ROW()-1)/4),4)+1

Should work ok.

 

[Mary]

Thank you so much both.

You're right:
=MOD(INT((ROW()-1)/4),4)+1

Should work ok.