Possible without Array Formulas???
Posted by Zif on April 18, 2001 3:10 AM
I have a large database that I need to summarise on a monthly basis.
The part of the database that I need to summarise is as follows:
Column G: Date
Column M: Amount
Column AB: Division
I need to be able to summarise the data in a table showing the number and value of all entries by division by month (not by individual date).
I have managed to do this using array formulas, but the databse is quite large and expands by up to 50 entries each day.
I tried using a pivot table, but I was unable to group the dates up to month level.
I cannot make changes to the original file otherwise I'd include a hidden column to extract Year and Month from the date and use that in the pivot table.
Any suggestions as to how I can summarise this data without using the slow (and getting slower!) array formulas??
Zif
Posted by Dave Hawley on April 18, 2001 3:26 AM
Hi Zif
I very glad you have posted this problem with array formulas. There are two people here that need to hear this! (hence my warning on my website about arrays)
But to the problem at hand. There shouldn't be any reason why a Pivot Table wont do the job for you. It would definately be the best option. If you are unable to group by dates it is most likely because you have included blank cells or some/all the dates are not true dates.
The best method to overcome the blank cells is with the use of dynamic ranges (see the link "Dynamic Ranges" on my Website).
But a much better option than array formulas (in most cases) is the use of Excels Database formulas, eg; DSUM, DGET etc. These formulas are designed for extracting data that needs to meet multiple conditions from a Table. They WONT blow out your file size and/or slooowww down saving, opening, closing and recalculation, like arrays do.
DaveOzGrid Business Applications
Posted by Mark W. on April 18, 2001 6:48 AM
> I tried using a pivot table, but I was unable to group the dates up to month level.
Zif, I'm curious why you're having so much trouble
grouping dates? Should be a snap! By chance does
your date field contain blanks or text values?
Posted by Zif on April 18, 2001 7:41 AM
Yes, the date field did contain blanks.
I'm actually working on something else at the moment, but I'm going to have a try with these new-fangled "Dynamic Range" thingies that Dave H keeps going on about :-))
I'll post again if I'm still stuck.
Zif
Posted by Mark W. on April 18, 2001 8:20 AM
> Yes, the date field did contain blanks.
That's why your date grouping didn't work. You
can get around this problem by filling these
blank values with an impossibly early date (e.g.,
1/1/1904) and then excluding them from your
PivotTable by setting the Grouping "Starting at"
date to an appropriate value.
Posted by Mark W. on April 18, 2001 8:46 AM
One more thing...
Since your existing PivotTable "sees" blanks in the
domain of your date field, you will need to create
a fresh PivotTable after you've filled in the
missing (blank) dates with a dummy date value
as suggested below. Only then will you be able
to group the dates into monthly values. > Yes, the date field did contain blanks. That's why your date grouping didn't work. You
