software

Given that my excel skills are limited at present, I have a task for work
that I hope you can help with.

A series of saved excel files that are based on weeks - Monday to Sunday.
The sheets are arranged with 1st column being a store with various stores
runing down in rows(not each week shows the same sytores however there are
many times where the same store is shown in seperate sorkbooks). The days of
the week are also aranged in columns- Mon, Tue Wed, Thur, etc.

The cells below each day for the store shows total sales .

So the workbook for the week will show the total sales per store per day.

Now I have multiple weeks and want to see what TRENDS store (A) does on
Mondays ONLY and then Tuesday ONLY etc over the many weeks in order to see if
one day is better than another for that store and then repeat for each store
(A) to (L)

Then do for each store and then clusters of stores which make up regions.

Is this possible easily?

Please advise your options and approach for me . I have thought of and tried
consolidate feature but that just added Monday store (A) of week 1 to same
for week 2 etc .

Thanks

Michael

Hi Mike

One way would be to copy data from each of your separate files to one
sheet in an excel workbook.
I assume each sheet currently has 8 columns, one for store name and 7
for the days of the week and since you mention stores A-L then it sounds
as though there would only be about 12 lines of data on each source
sheet.
On the new Sheet being created, add a 9th column called Week Number.
Now, as you copy each successive block of data, fill in column 9 with
the week number that it relates to.

If you have a huge number of files to copy from, then Ron de Bruin has
some nice macro solutions to automate this task
www.rondebruin.nl/copy3.htm
I think you would probably need
www.rondebruin.nl/copy3.htm#header

Now with you new consolidated data, set up a Pivot Table, which will
allow you to carry out any of the analyses you require.
For more information on setting up Pivot Tables, take a look at Debra
Dalgleish's site
www.contextures.com/tiptech.html
and scroll to the section on Pivot Tables

I hope this guides you in the right direction.

--
Regards

Roger Govierquot;MikeR-Ozquot; gt; wrote in message
...
gt; Given that my excel skills are limited at present, I have a task for
gt; work
gt; that I hope you can help with.
gt;
gt; A series of saved excel files that are based on weeks - Monday to
gt; Sunday.
gt; The sheets are arranged with 1st column being a store with various
gt; stores
gt; runing down in rows(not each week shows the same sytores however there
gt; are
gt; many times where the same store is shown in seperate sorkbooks). The
gt; days of
gt; the week are also aranged in columns- Mon, Tue Wed, Thur, etc.
gt;
gt; The cells below each day for the store shows total sales .
gt;
gt; So the workbook for the week will show the total sales per store per
gt; day.
gt;
gt; Now I have multiple weeks and want to see what TRENDS store (A) does
gt; on
gt; Mondays ONLY and then Tuesday ONLY etc over the many weeks in order to
gt; see if
gt; one day is better than another for that store and then repeat for each
gt; store
gt; (A) to (L)
gt;
gt; Then do for each store and then clusters of stores which make up
gt; regions.
gt;
gt; Is this possible easily?
gt;
gt; Please advise your options and approach for me . I have thought of and
gt; tried
gt; consolidate feature but that just added Monday store (A) of week 1 to
gt; same
gt; for week 2 etc .
gt;
gt; Thanks
gt;
gt; Michael
Thanks Roger for your efforts - I will work through what you have said and
referred me to, but at first glance it looks a bit beyond my knowledge of
excel - but will try -

Thanks for the pont in the right direction.
Mike

quot;Roger Govierquot; wrote:

gt; Hi Mike
gt;
gt; One way would be to copy data from each of your separate files to one
gt; sheet in an excel workbook.
gt; I assume each sheet currently has 8 columns, one for store name and 7
gt; for the days of the week and since you mention stores A-L then it sounds
gt; as though there would only be about 12 lines of data on each source
gt; sheet.
gt; On the new Sheet being created, add a 9th column called Week Number.
gt; Now, as you copy each successive block of data, fill in column 9 with
gt; the week number that it relates to.
gt;
gt; If you have a huge number of files to copy from, then Ron de Bruin has
gt; some nice macro solutions to automate this task
gt; www.rondebruin.nl/copy3.htm
gt; I think you would probably need
gt; www.rondebruin.nl/copy3.htm#header
gt;
gt; Now with you new consolidated data, set up a Pivot Table, which will
gt; allow you to carry out any of the analyses you require.
gt; For more information on setting up Pivot Tables, take a look at Debra
gt; Dalgleish's site
gt; www.contextures.com/tiptech.html
gt; and scroll to the section on Pivot Tables
gt;
gt; I hope this guides you in the right direction.
gt;
gt; --
gt; Regards
gt;
gt; Roger Govier
gt;
gt;
gt; quot;MikeR-Ozquot; gt; wrote in message
gt; ...
gt; gt; Given that my excel skills are limited at present, I have a task for
gt; gt; work
gt; gt; that I hope you can help with.
gt; gt;
gt; gt; A series of saved excel files that are based on weeks - Monday to
gt; gt; Sunday.
gt; gt; The sheets are arranged with 1st column being a store with various
gt; gt; stores
gt; gt; runing down in rows(not each week shows the same sytores however there
gt; gt; are
gt; gt; many times where the same store is shown in seperate sorkbooks). The
gt; gt; days of
gt; gt; the week are also aranged in columns- Mon, Tue Wed, Thur, etc.
gt; gt;
gt; gt; The cells below each day for the store shows total sales .
gt; gt;
gt; gt; So the workbook for the week will show the total sales per store per
gt; gt; day.
gt; gt;
gt; gt; Now I have multiple weeks and want to see what TRENDS store (A) does
gt; gt; on
gt; gt; Mondays ONLY and then Tuesday ONLY etc over the many weeks in order to
gt; gt; see if
gt; gt; one day is better than another for that store and then repeat for each
gt; gt; store
gt; gt; (A) to (L)
gt; gt;
gt; gt; Then do for each store and then clusters of stores which make up
gt; gt; regions.
gt; gt;
gt; gt; Is this possible easily?
gt; gt;
gt; gt; Please advise your options and approach for me . I have thought of and
gt; gt; tried
gt; gt; consolidate feature but that just added Monday store (A) of week 1 to
gt; gt; same
gt; gt; for week 2 etc .
gt; gt;
gt; gt; Thanks
gt; gt;
gt; gt; Michael
gt;
gt;
gt;