comparing data between 2 sheets

**pengwin** · 04-01-2010, 01:48 PM

I need to compare data from 2 excel worksheets (sales data from 2 separate years). There are 4 columns (Region, Category, Sales). I need to compare the sales based on category in a specific region. IE: Sales for Category 1 in Region 1 are $1000 more than the previous year.

I've attached a sample of the data, I need to compare 2 sheets similar to that, I would like the comparison to come out like this:

Please Login or Register  to view this content.

any ideas? someone suggested vlookup but im not really sure how to use that either and wanted a 2nd opinion before i started researching a lost cause.

edit: so that data looks a lot better in my post view..fixed with code tags

**NBVC** · 04-01-2010, 02:05 PM

First you would need to do a lookup to add the 2008 dates in a new column on the same sheet as the 2007 dates... then you could do a pivot table that would assemble the information as you have shown.

Say you have a sheet 2 that looks like the sheet you supplied (which by the way is a .csv file.. and should be saved as a .xls file to prevent problems).

Then you would apply a formula to get 2008 numbers...

=Sumproduct(--('Sheet2'!$A$2:$A$250=A2),--('Sheet2'!$B$2:$B$250=B2),'Sheet2'!$C$2:$C$250)

where Sheet2 is the name of the other sheet.. adjust also the ranges to ensure you get all the database... then copy the formula down.

Change the headers to 2007 and 2008 (or whatever to distinguish).

Then select the whole range (all columns to last row)..

Go to Data|Pivot Table and Pivot Chart... click Next... Ensure your range is correct, click Next... click Layout... drag the Region over to the Row Area, Drag the Category over to the Row Area too, then drag the 2007 data over to the Data area and again drag the 2008 data over to the data area.

When you drag over to the data area ensure they say Sum of 2007 and Sum of 2008, respectively. If not, double-click and change to Sum.

If the years appear underneath each other, click the DATA gray box at the top of the pivot table, and just drag it over to next column... years should be next to each other now.

Next, go to View|Toolbars and select Pivot Table... a new toolbar should show.

Click on the Pivot Table dropdown and select Formulas|Calculated Field

Enter a Name and then enter a formula =2008-2007 (whatever the titles of the columns were).

Click ADD.. and click Ok.

A bit more on Pivot Tables... http://peltiertech.com/Excel/Pivots/pivotstart.htm

**pengwin** · 04-01-2010, 02:29 PM

is there any other way to do this other than pivot tables? aren't there macros which can recognize that if a specific row has a cell with "atlantic" and "south" in it. it needs to grab the number from the 5th column and add it to a specific cell in a separate sheet?

**NBVC** · 04-01-2010, 02:33 PM

I am sure there is.. Pivot Table is far more efficient though.

If you still insist.. we will have to hope that someone comes around that is willing to do that... I am not a VBA expert.

**pengwin** · 04-01-2010, 02:38 PM

ok, so then my question is....currently, i run a macro which copies the csv files into a tab. if i use pivot tables can i have the tables automatically update the data even if the tab name is different or will i have to create a new pivot table each time the name of the tab(worksheet) changes.

**pengwin** · 04-01-2010, 04:07 PM

ok, good news, i got the pivot table method to work, now all I need it to do is to auto-refresh itself when new data is loaded into the pivot table. would anyone one know how to do this? i check the "refresh data when opening the file" but is there anyway for it to constantly check for updated data?