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

0
Utf
3/5/2010 3:39:01 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
891 Views

Similar Articles

[PageSpeed] 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" <Mary@discussions.microsoft.com> wrote in message 
news:70C688D1-4077-48E0-A86D-F6512B077654@microsoft.com...
> 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
> 


0
Luke
3/5/2010 4:15:18 PM
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
0
Dave
3/5/2010 4:22:40 PM
Dave,

OP wanted column B to repeat numbers 1-4.

-- 
Best Regards,

Luke M
"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message 
news:4B912FD0.FF57342A@verizonXSPAM.net...
> 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 


0
Luke
3/5/2010 5:22:11 PM
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" <petersod@verizonXSPAM.net> wrote in message
> news:4B912FD0.FF57342A@verizonXSPAM.net...
> > 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
0
Dave
3/5/2010 5:52:05 PM
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" <petersod@verizonXSPAM.net> wrote in message
> > news:4B912FD0.FF57342A@verizonXSPAM.net...
> > > 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
> .
> 
0
Utf
3/5/2010 7:14:06 PM
Reply:

Similar Artilces:

Smart List Help
I am trying to create a Smartlist for receivables aging and I having some difficulty. I want to be able to see all customers with an outstanding balance and the associate the outstanding unpaid documents to the customer. I can get the aging information but I am unable to get the documents that responsible for the balance. I can get the information from I need that is displayed but I can’t get the information from “receivables transaction inquiry” window I am looking for. I want to be able to show any document with a dollar amount in the remaining amount field and only show the document...

Formula Help #40
am using a way to do the following column1 column 2 column 3 (2+3)+(3*5) 5+15 20 in the first column I want to be able to see the expresion in the second column I want to have the result of each parenthesis and in the final column the result Please help Thanks Stelios Stelios, Here is one solution: Break down your equation so each part is in it own column. Column A Column B Column C etc. ( 2 + etc. Somewhere on the same worksheet list the numbers(0 -9) and the operators (*, /, +, - ...

Multi Column Report Help
Hi All, I am entering timecards into a database. On the timecard I have Truck_Num, Material, Loads (simplified) I have a query that gives me the data I want on the report. This is what the query looks like 101 A 10 101 B 5 101 C 7 102 A 5 102 C 5 etc. Is there a way to make my report look like the following: Truck A B C Total 101 10 5 7 22 102 0 5 5 10 Total 10 10 12 32 After typing this out, I realized that maybe excel would be better for this. What do you think? Chad ...

Div 0 help
=(SUM(E98:F98)+D98/2)/(I98-G98) This formula gives me a Div 0 error. How can I re-write it so that it stores a value of 0 , if it encounters a Divide by 0 and executes the calculation if >0 Help appreciated Joe maybe: =if(i98=g98,0,sum(e98:F98,d98/2)/(i98-g98)) I combined your addition into the =sum() portion. I thought it made it easier to read. On 05/17/2010 06:12, joey_boy wrote: > =(SUM(E98:F98)+D98/2)/(I98-G98) > > This formula gives me a Div 0 error. How can I re-write it so that it stores > a value of 0 , if it encounters a Divide by 0 and e...

help
looking for driving game called easter egg i'm told that software designers include one thats a secret message,and need to find the one for microsoft excel 2000 sforward, http://j-walk.com/ss/excel/odd/odd01.htm John "sforward" <anonymous@discussions.microsoft.com> wrote in message news:C5DD4242-ED01-47B1-9AD1-E584626F7E63@microsoft.com... > looking for driving game called easter egg i'm told that software designers include one thats a secret message,and need to find the one for microsoft excel 2000 Hi sforward! Try: John Walkenbach: http://j-walk.com/ss/ex...

Out of Disk Space........ Help!!
Suddenly I am out of disk space. It seems that the IMC OUT Archive has grown so large you cannot even do a dir listing of it. How do I clear the archive files to reclaim space ? Thanks You can safely delete contents of IN\ARCHIVE and OUT\ARCHIVE folders. You also can disable message archival if you do not need it in IMC props\Diag logging tab. froggy wrote: > Suddenly I am out of disk space. It seems that the IMC > OUT Archive has grown so large you cannot even do a dir > listing of it. How do I clear the archive files to > reclaim space ? > > Thanks To help complete...

Pivot table help #11
I needed help with a Pivot table. In the attached excel sheet i am trying to use the Creatiion date (coloum X)as one of the filters in the pivot table. Could someone please guide me as to how i can filter with the Creation date as one of the pivot tables. Thanks Sammy ...

Can You Help Me Please....
I have been given the job of booking vouchers in at work.....they come in pads of 20 and i have to list 200 books manually once a month. The vouchers are all serial numbered and have to be booked in by serial number and as I issue them ,they are booked out (you still with me?) The voucher numbers will be something like 123501 - 123520, then the next pad is 123521 to 123540 etc etc. When I get them in I get an invoice which shows me the start and end numbers, i'd love to try and set something up where i could enter the start and end serial number and excel would create a sheet with all...

help with database design required
Hello Everyone, I have a system that has to track the movement of tools from in and out of the stores. Borrower Details: tblname: id-PK,name,department tbltoolmaster ToolID-PK Toolno ToolDesc Location tbltooltransaction Tranid-PK TrantoolID - FK NameID - FK DateTaken DateReturned what i am attempting to do is for every tool borrowed from the tool store i need to be able to record the date taken and date returned. If the date returned is not null - Tool is Available Once the tool is burrowed by a person A on a given date than the status should be updated to loaned and when he returns ...

Help on Help
Hi: I'm trying to create a Help system for a vc++ (vc.net 2003) dialog-based application. I started by creating a separate single document project where I included "Context-sensitive Help" and copied the help files (MyApp.hpj, afxcore.rtf, afxprint.rtf, bullet.bmp, and MyApp.cnt) over to my application. I now have a Help Topics box that I can bring up with Contents, Index and Find tabs. I can change the books in the Contents tab by modifying the MyApp.cnt file and I can change the text that is pointed to by finding the original text in the afxcore.rtf file and changing it t...

Macro help
Hi all, is there a way to copy the name a user inputs (say in cell ref A1) and create a new worksheet within my current workbook with this 'name' using a macro ??. ie the user inputs Bloggs,Joe into cell ref A1, and once the macro is selected a new worksheet is created with the 'tab' now changed to Bloggs,Joe - instead of Sheet1, Sheet2 etc etc thanks PSA I'm a novice -so sorry if this is simple One way: Sub NewSheet() Dim x As Worksheet Set x = ActiveSheet Sheets.Add ActiveSheet.Name = x.Range("A1") End Sub -- tj "Anthony" wrote: ...

Damsel in distress needs VBA help!!!
Hi, I have 45 customer service reps weekly schedules in an excel spreadsheet. They all are on one of 4 teams and are listed alphabetically as so: Doe,John start time break 1 lunch break 2 Monday 8:00 10:15 1:30 3:15 Tuesday Weds etc.. I need them to be sorted by team. I think a macro for each team would be the easiest way to do this. I need code that would go through the alphabetical list and search for an agents name and delte that row and the 10 rows below it. I have no idea where to begin or even how to be able to set...

Help me please #2
I have a sheet formatted to wrap text with several entries with ten or 15 lines. I select the whole sheet and format>rowheight>autofit selection. Then switch to page break preview and the text wrap changes and the text is truncated when printing. How do I fix this? If the text in each cell is more than 1024 characters, it will truncate. Carole O "ArchiTorture" wrote: > I have a sheet formatted to wrap text with several entries with ten or 15 > lines. > > I select the whole sheet and format>rowheight>autofit selection. > > Then switch to page ...

STMP ERROR HELP
Hi, I set up a bt wireless network, I use BT as my email provider but my partner uses dircon. I am trying to get the dircon email to go through outlook xp on our notebook. I had this set up a couple of years ago but recently formatted and reinstalled the software on the notebook but can't remember how I did it. It's receiving email but will not send. I've tried setting up the STMP address as BT's and Dircon's but neither work. Any suggestions? Cheers, Alex are you authenticating with the outgoing mail server? -- Diane Poremsky [MVP - Outlook] Author, Teach Yo...

pivot table
i have a pivot table that calculates resource availability. each resource may work on multiple projects, and each of these projects require a certain number of hours. the grouping is by person, by project. for each person, i need to show hours allocated toward each project (detail) and then total number of hours allocated (subtotal). i also need to show a calculated field of % hours available (subtotal only) for each person. John project 1 30 hours (detail) project 2 15 hours (detail) Sum of hours 45 hours (subtotal) Caculated field (1-sum(hours)/40) (s...

Plz...Help
Dear Guys, I made a vacation database in which I have vacation form to put the employee request. But before I filling the vacation form I want to check how many times the employee took the vacation in the current year i. e. when I select the name or ID of the employee in the vacation form a message should show in the text box of the form that how many times and days he took the vacation in the current year. I have 2 tables Emp and vacation with one to many relationship I tried to solve it in many ways I didn’t . Please help to solve it. Regards Nad - You can display the ...

Need help looking up data-vlookup isn't working
I have 2 worksheets. Column A in sheet 1 contains values that I know are in column A of sheet 2. I need only the rest of the values in sheet 2 (in other words, the values that are NOT in sheet one). Thanks! Put this in Sheet2 cell B1 and copy down =IF(ISNA(VLOOKUP(A1,Sheet1!A1,1,FALSE)),A1,"") Vaya con Dios, Chuck, CABGx3 "MMcGee" <MMcGee@discussions.microsoft.com> wrote in message news:8B3F7255-3691-4CE3-AB21-B625764F6A91@microsoft.com... > > I have 2 worksheets. Column A in sheet 1 contains values that I know are > ...

Excel newbie needs help from math wiz (poker related)
I want to create a Excel workbook that will help in specific poker situations. Short version: I want to be able to calculate how often my opponent will need to fold for an all-in raise to show a positive expectation. Explanation: In poker you can win a hand by having the best hand at the end OR getting your opponent to fold. A common situation comes up where you have a "drawing hand" and face a bet by your opponent. In this situation you know you are currently behind in the hand, but have a chance to win if you catch some of your "outs" (cards that will give you th...

help...help...help
I just installed Microsoft Office XP Professionaql with no problem. However, whenever I try to perform any task such as opening contacts area in order to create an entry, I receive a dialog box with Microsoft Outlook and a yellow exclamation point. Also, included in the dialog box are the words could not open the item, try again. Other information that might be important is that I use a pst file. The error message also occurs when I try to open the Contact folder from the folder list as well as when I try to perform any function. It was a clean install not an upgrade. Could you ...

Help with if statement
I have used the following statement, but it is returning an incorrect result for some cells. In an cell containing an age of 10 years, 11months..the formula is returning "6m-2y" when it should say older than 2. It also does this for the age 25 years 6 months and 18 years 10 months. Please help. =IF(AND(E203>="0years,0months",E203<="0years,3months"),"0-3",IF(AND(E203>"0years,3months",E203<="0years,6months"),"3-6",IF(AND(E203>"0years,6months",E203<="2years,0months"),"6m ...

Help
Hi All, I'm posting out as I've been having some issues with 3 nearly identical clients ever since putting in SBS2008. They have proven very difficult to trace and resolve. Client 1 - SBS2008 new install, new domain etc on HPML110, 5 users Client 2 - SBS2008 migrated from SBS2003 via MS Migration on HPML150, 15 users with all new HP/WinXP workstations Client 3 - SBS2008 migrated from SBS2003 via Swing It! migration, on HP ML350G6, also about 15 users. All 3 have Sonicwall TZ series firewalls, HP gigabit switches, and use Windows XP and Office 2003. All use Exchange ...

Strange build issue
Hi, I inherited the maintenance of an application that was probably intially created using VS 6. It is written using MFC. I recently converted this to VS2005 with minimal problems. One thing that has come up now is that every time I run the app within VS 2005 it says that the project is out of date and wants to build it even though I have not made any changes. If I click Yes, it will then pop-up a dialog that says, windows cannot find hcw make sure you typed the name correctly and try again. Does not affect the application, more bothersome than anything. I have posted this problem once before,...

Query Help
Ok, I am trying to write a query that basically calculates the same data as the HQ Report labeled "Detailed Sales Report". The only number I want is the final total sales run between two dates. This information is going on a custom report I am creating for my CFO. The query I have so far is this: SELECT sum(transactionentry.price) AS price FROM TransactionEntry INNER JOIN [Transaction] WITH(NOLOCK) ON TransactionEntry.TransactionNumber = [Transaction].TransactionNumber AND TransactionEntry.StoreID = [Transaction].StoreID INNER JOIN Batch WITH(NOLOCK) ON [Transaction].BatchNu...

Help needed on 3 table query
SQL 2008 I have three tables which I need to get the following result from. Can anyone help CREATE TABLE [dbo].[tbBOSS_client]( [Licence] [varchar](6) NOT NULL, [CompanyName] [varchar](256) NOT NULL, [Status] [varchar](32) NULL, CONSTRAINT [PK_tbBOSS_client] PRIMARY KEY CLUSTERED ( [Licence] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] CREATE TABLE [dbo].[tbBOSS_ClientService]( [Licence] [varchar](6) NOT NULL, [ServiceID] [int] NOT NULL, [AUDDISEnabl...

pls Help-Smart list reference
hi I need help in something I think it is so simple in the screen of Sales Order Setup "from tools-->setup-->sales-->sales order processing-->button Order" I have added City field to hold the city of that order and with lookup button to open the the user class lookup ""as I have the city = user class "" so I want the value of the user class to be retrieved in the city textbox and saved in external table "I already save it in external table" then when I choose the order that is saved before from the lookup window of sales types id ,i...