Main data sheet convert to high level

  • Thread starter Thread starter Nelson
  • Start date Start date
N

Nelson

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?
 
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.
 
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)*(Date_from_Data_Sheet>=DATE(RIGHT(C$1,4),1,1))*(Date_from_Data_Sheet<DATE(RIGHT(C$1,4)+1,1,1)),(Amount_from_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.
 
Back
Top