Compare Data from two Worksheets

[mcarrigg]

Hi Guys,

Thanks in advance for any help you may be able to offer.

I have a workbook with three sheets. "Comparison", "Last year" & "This Year".

Each sheet will only have one calendar month of data in it (ie 1st Aug
through to 31st Aug, 1 row per day)

I would post the sheet here to view however I am new here and cant seem to
find a link to attach a sheet.

I would like to import "Last Year" & "This Year" into "Comparison". The
problem I am having is that I would like to import the 1st day of month of
2008 into "Comparison". Say for arguments sake that this day is a Friday for
instance, I would like to import the first friday of "Last Year" and import
it into "Comparison" into the cell above the imported "This Year" row. In
the row below these two rows, I would like a row calculating the difference
between the two days of data.

I would like this repeated so that "Comparison" is comparing each day from
there on in for the whole months' data.

ie, "Comparison" rows should be something like...

Date Day PULP ULP Diesel Total Fuel

03-Aug-07 Friday 1,381 7,746 4,799 13,926
01-Aug-08 Friday 2,834 13,662 13,191 29,687
Difference 1,453 5,916 8,392 15,761
04-Aug-07 Saturday 943 5,883 2,767 9,593
02-Aug-08 Saturday 1,288 8,094 8,588 17,970
Difference 345 2,211 5,821 8,377
05-Aug-07 Sunday 1,497 10,880 11,969 24,346
03-Aug-08 Sunday 780 6,022 5,143 11,945
Difference -717 -4,858 -6,826 -12,401

I want this to automatically import so that I can change the month to
September and send out to the sites and it automatically compares the right
days for whatever month I put in.

Thanks heaps again!

 

[OssieMac]

It would be much easier if you use a different format for the comparison
worksheet. persohnally I think that it will be even easier to read and follow.

Copy all of the 2008 data to the comparison sheet so that it is in columns A
to F.

Then select the 2007 data from the first matching day, say Fri, and copy
that data and paste it alongside last years data in columns G to L so that
the days of the week are all on the same rows as the 2007 days of the week.

Insert the difference formulas in the columns to the right.

 

[mcarrigg]

Hi Ossiemac,

I thought of doing that, but in the example above, I have trimmed the sheet
down, I actually have about 25 columns of data, so if we are to do that
format, I would have 75+ columns.

Don't get me wrong presenting it in that format is still an option, but I
was just hoping that there was a away to isolate each days data and present
it in a somewhat more printer friendly format.

If the solution I am hoping for cannot be done, then Ill definately go for
this option though.

Thanks for the thoughts though

Cheers,

Matt

 

[OssieMac]

Another suggestion. How many of the columns can you hide so that you just
print the relevant data? I believe that if you think about it then quite a
few can be eliminated from the printed report. Having been a business
analyst, I know that much of the data used to calculate results is
superflouous to the user of the information. In my experience the user would
much rather see a chart (graph) of the results and very few can absorb large
amounts of data presented as figures.

For example, I doubt that the last years date for the day of the week is
relevant to the user. They just need to know that the comparison is based on
the days of the week in the month. Next the month can be in the Header and
you can custom format the date column to "ddd dd" which will only display as
Fri 01 in one column istead of 2 and reduces the size of the column to that
required to display the entire date; (although the underlying date is still
in the cell).

Next is that it is highly unlikely that they are interested in last years
totals; just this years totals and the difference from last year.

Also it is so much easier to create charts (graphs) from the data if you
keep it all on one line.

 

[mcarrigg]

Industry is Petroleum Retail Stations and the sheet will be sent to 12 of my
sites.

Columns contain.
Date
ULP
PULP
DIST
LPG
EGEN95
E95
E98
TOTAL WETSTOCK
Oil / Lubricants
Hot Food
Cigs
Drinks
Sweets / Lollies
Flav Milk
Dairy
Grocery
Ice Cream
Newsagency
Chips / Nuts
Bakery / Breads
Accessories
Phone Cards
TOTAL SHOP
COMBOS SOLD


So you can see these are KPI indicators that I would like to track. I need
to track the days of the week as days are more relevant to me than dates (ie
fuel cycle in Wetstock Sales / Public Holiday Spikes for holidays like
easter, etc).

My Site managers at each site plug in the data from 2008 and 2007 from their
back office computers. I want the figures to see where big discrepancies
occur at a glance (ie if sales are down on same month in 2007, then I can
look at this report and might be able to see that a couple of days may be
down significantly, then i can look into why these days are down (ie wet
weather on these days results in less traffic through the site). The 2008
data also gives me a snapshot of the months performance before all the
monthly reports filter in.

Sorry for the long winded reply, but these are my goals for this
spreadsheet. Basically it is a tool to keep an eye on performance against
previous benchmarks and forecasts.

If you can think of something better to help, then i am all ears!