Help w/ summarizing a mess of excel data from different sheets?

[Matt G.]

I'm not sure if this is the right place to post this. I really
appreciate any
and all help from you experts!!

So here's the example: I have a mess of data sheets with sales numbers
from a lemonade stand. The sheets list the seller in rows (Johnny,
Bobby, Sue), and then have several columns for each seller listing what

each seller sold: lemonade, coke, pretzels...and how many of each of
those they sold. These sellers and their sales numbers are listed on
several worksheets in several workbooks from different weekends and
different seasons.


I need to combine all this information to make a production sheet for
each year and hopefully repeat for years to come.


One of the issues is combining duplicate entries: Rather than updating
his single row, Johnny made a new entry named "Johnny" every time he
worked a shift, so there are many "Johnny" rows that have small sales
numbers; these need to be combined and they exist on several different
workbooks and worksheets.

 

[Otto Moehrbach]

Matt
That sounds like quite a project. Such a project will necessitate VBA
(automation). I'll try to help you with it but you're going to have to come
up with a representative sample of files/workbooks with each containing a
representative sample of data. Just fake the data, all I need is the
layout. Then make up a workbook/file that shows the layout of the final
product you want. Then send me all those files with any further
explanations you can provide. Also tell me the version of Excel you are
using.
Because email servers have limitations on the size of any one message,
you may need to send it in several emails. I suggest you send me a listing,
in a separate email, of what you are sending so that I can tell if I got it
all. My email address is (e-mail address removed). Remove the "nop" from this
address. HTH Otto

 

[Matt G.]

Thanks Otto, sent you an email!

I'm putting a link to a screen shot of some fake data; it might be hard
to read but hopefully you get the idea:

http://i21.photobucket.com/albums/b265/mattg6/Z1/Lemonade.jpg

Essentially I have 2 workbooks that have several worksheets with data
just like what I'm attaching. Using the example sheet:

1.) In each worksheet I need to condense each salesperson's data. In
other words, Johnny's name should be listed once for each client and
then I would need his total sales to each client--that is, the sum of
lemonade cups, coke cans, and pretzels sold to client Smith, then
client Johnson, etc.
2.) And then, take the condensed data of each salesperson's activity
(created in step 1) and put all the salespeople's data onto one summary
sheet.


Thanks!!

 

[Matt G.]

Also, there are about 20 Salespeople but hundreds of clients....

 

[Guest]

You don't say how many worksheets/books you have to summarise.

If it's hundreds of them then automation is the only way to go, but if we
are talking a dozen or so, or anything around that, then personally i would
probably just dump them all into a single worksheet, one under the other and
then throw a Pivot table at it.

Now, if you want the total number of items, you will need to either:-

Add a total column to your data that sums the 3 items,

or (and my preference),

is duplicate your data 3 times over, one under the other (Only when you have
consolidated them all though - only need to do it once), and in a new column
titled Refreshment give each section of data it's own flag saying either
Lemonade/Coke/Pretzel, then delete two items from each section of data, (eg
from the Lemonade section delete the Coke and Pretzels data, from the Coke
section delete the Lemonade and Pretzels data) such that instead of this

SP ID Client L C P
John 1 Smith 2 3 1
John 1 Smith 3 1 2
John 1 Smith 4 2 2
John 1 Smith 3 3 3

you get this:-


SP ID Client Amt Ref
John 1 Smith 2 Lem
John 1 Smith 3 Lem
John 1 Smith 4 Lem
John 1 Smith 3 Lem
John 1 Smith 3 Coke
John 1 Smith 1 Coke
John 1 Smith 2 Coke
John 1 Smith 3 Coke
John 1 Smith 1 Pretzel
John 1 Smith 2 Pretzel
John 1 Smith 2 Pretzel
John 1 Smith 3 Pretzel

A pivot table would then allow you to analyse that lot in literally no more
than a minute.

Select all data, do Data / Pivot table and chart report / Next / Next / Finish

Drag Salesman into ROW field, Drag Refreshment into COLUMN field, Drag Amt
into DATA field, and then drag Client to BETWEEN ROW and DATA field. Done.

Doing it this way also allows you to analyse the breakdown of how much of
each product was actually sold, and by who.