ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Help to build a table (https://www.excelbanter.com/excel-discussion-misc-queries/258088-help-build-table.html)

Mary
Help to build a table
 
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[_4_]
Help to build a table
 
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

Dave Peterson
Help to build a table
 
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

Luke M[_4_]
Help to build a table
 
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
Help to build a table
 
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

Mary
Help to build a table
 
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
.


All times are GMT +1. The time now is 12:33 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com