Just to close out this question, Pete's suggestion worked like a charm,
though my original spreadsheet was so big that replicating formulas (I
used SUMIF to total up the depreciable values of the various sections
of each street) would hang up my computer for up to 10 minutes or so.
Thanks very much.
John

On Jan 23, 6:33 pm, "Pete_UK" wrote:
Hi John,

an alternative approach is to use a helper column in your main data and
join the Street, Date and Type data together, eg = street & date &
type, whatever your columns are. Copy this down to the bottom of the
data, and then fix the values using Paste Special. Then copy this
composite column, with a heading, to your second sheet and apply Data |
Filter | Advanced Filter to it to select Unique Records only and Filter
in Place. Then in this second sheet you can apply a COUNTIF formula to
count the number of records of each composite from the main data - you
can then fix your values and you have your "squashed" records - you
could delete the original data and use File | Save As to give this file
a new name.

Hope this helps.

Pete

On Jan 23, 7:30 pm, wrote:



I'm trying to condense a very large spreadsheet by summing multiple
entries of the same type and copying that total entry to a second
worksheet. The worksheet is a listing of all of the streets in
Appleton, the year in which work was done to each, and what kind of
paving that work was. There are multiple records for each street
because the information was originally recorded by block. All we need
for our audit is the total for each street by type and year (e.g.
Appleton St., 1975, concrete; not a separate record for each block that
was paved). I've sorted the spreadsheet and recorded a macro that
inserts a couple of blank lines at each change of year or pavement,
copies the last previous line, and inserts a formula to total all of
the amount columns (i.e. length, base value, improvements, accumulated
depreciation, etc.).

My problem is in the formula. I recorded "=sum(up 1 to end-up)" and
got "=sum(R[-4]C,R[-1]C)" ; a fixed range of the preceding 4 cells.
How can I make that avariablereference so I'll get the total of the
rows preceeding the formula up to the last previous blank row?

Thanks.

John- Hide quoted text -- Show quoted text -