Duplicates and subtotals

  • Thread starter Thread starter Deanna
  • Start date Start date
D

Deanna

Each week we receive a file from our payroll system,
approx 6000 rows. Contains General Ledger data by
employee and GL account with amounts. We use this file to
post to our GL system. I'd like to find a way in Excel
to subtotal by row containing unique data.

Emp Co Accnt date amount
1234 10 4000 10/17/03 100.00
1234 10 4000 10/17/03 5.00
I would like to see, instead of two lines of detail,
1234 10 4000 10/17/03 105.00

Anyone had the opportunity to do this?
Thanks in advance
Deanna
 
I'd insert a new column A and use a helper formula:
(assuming headers in row 1 and date in A:E)

=a2&"-"&b2&"-"&c2&"-"&text(d2,"yyyy/mm/dd")

then sort by this column and do Data|Subtotals.
You can use the outlining symbols at the left to hide the details.

==
An alternative would be to use Data|Pivottable.

You could drag the emp, co, accnt, and date to the row field and drag amount to
the data field.

After you see the pivottable, double click on each of the row fields and select
None for subtotals.
 
Back
Top