Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Main data sheet convert to high level
I have a data sheet that pulls all data I need, trouble is the same company
can have multiple enties Now I want to create a clean high level sheet, using the customer ID number pull the customers name, customer number and add up the customers costs into one cell for each year 2008,2009. So I would then see in Cells A1 - D1 Customer name - Number - Maint paid 2008 - maint paid 2009 Can this be done? -- Nelson |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Main data sheet convert to high level
You don't need a macro to do this Or you can record a macro will performing
these steps 1) Get a unique list of customer Numbers. Select the column where the User Numbers are located. 2) Go to menu Data - Filter - Advance Select unique and Copy to new location. Select the Copy to range on your new worksheet. 3) Now on new worksheet use a vllokup formula to get the other values in the table If you new sheet is sheet2 and the original is sheet 1 then In cell B2 on new worksheet =vlookup(A2,Sheet1!A$1:D10000,2) 4) use sumif to get the values In cell C2 on new worksheet =sumif(Sheet1!A$1:A10000,A2,Sheet1!C$1:C10000) In cell D2 on new worksheet =sumif(Sheet1!A$1:A10000,A2,Sheet1!D$1:D10000) Then copy the 3 formula down the new worksheet. I have a data sheet that pulls all data I need, trouble is the same company can have multiple enties Now I want to create a clean high level sheet, using the customer ID number pull the customers name, customer number and add up the customers costs into one cell for each year 2008,2009. So I would then see in Cells A1 - D1 Customer name - Number - Maint paid 2008 - maint paid 2009 Can this be done? -- Nelson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Main data sheet convert to high level
Hi Nelson,
Is your source data organized as follows?: Column A = Customer Number Column B = Customer Name Column C = Date Column D = Amount Do you want the high level sheet summarized as follows?: Column A = Customer Name Column B = Customer Number Column C = Maintenance Paid 2008 Column D = Maintenance Paid 2009 If so, on the high level worksheet: Cell A2: =VLOOKUP(B2,'Source Data'!A:B,2,FALSE) Cell C2, the following is an array formula, and must be entered with Ctrl+Shift+Enter, which will create curly brackets before and after the formula: =SUM(IF((Customer_Number_from_Data_Sheet=$B2)*(Dat e_from_Data_Sheet=DATE(RIGHT(C$1,4),1,1))*(Date_f rom_Data_Sheet<DATE(RIGHT(C$1,4)+1,1,1)),(Amount_f rom_Data_Sheet))) Cell D2: copy formula from cell C2 note that the right function is looking at the column head in column C of the high level worksheet; this assumes that the year 2008 or 2009 are the last 4 characters in the column head. Hope this helps. "Nelson" wrote: I have a data sheet that pulls all data I need, trouble is the same company can have multiple enties Now I want to create a clean high level sheet, using the customer ID number pull the customers name, customer number and add up the customers costs into one cell for each year 2008,2009. So I would then see in Cells A1 - D1 Customer name - Number - Maint paid 2008 - maint paid 2009 Can this be done? -- Nelson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Security Level - High Priority | Excel Programming | |||
Using macro to convert single level BOM to Multi Level BOM | Excel Programming | |||
Set the security level to high | Excel Programming | |||
How to change Macro security level very high to low... | Excel Programming | |||
How to change Macro security level very high to low... | Excel Programming |