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

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

--
Best Regards,

Luke M
"Mary" wrote in message
...
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

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.



Mary wrote:

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

--

Dave Peterson

Dave,

OP wanted column B to repeat numbers 1-4.

--
Best Regards,

Luke M
"Dave Peterson" wrote in message
...
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.



Mary wrote:

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

--

Dave Peterson

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

Should work ok.

Luke M wrote:

Dave,

OP wanted column B to repeat numbers 1-4.

--
Best Regards,

Luke M
"Dave Peterson" wrote in message
...
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.



Mary wrote:

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

--

Dave Peterson

--

Dave Peterson

Thank you so much both.

"Dave Peterson" wrote:

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

Should work ok.

Luke M wrote:

Dave,

OP wanted column B to repeat numbers 1-4.

--
Best Regards,

Luke M
"Dave Peterson" wrote in message
...
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.



Mary wrote:

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

--

Dave Peterson

--

Dave Peterson
.