Merging/Consolidating data?

[Guest]

My accounts package produces listings in form of CSV files. Typically the
listings are dates and (invoice)values. The dates are in order but non
sequential -in otherwords there are gaps. Some dates are repeated (think
'date, invoice value' where theres more then one invoice per day)

I regularly need to merge/consolidate two/more such listings into one so
that I end up with a dates list (without repeats) and value being the sum of
all invoice values for the (unique) dates referenced.

Note the dates and values differ from list to list but the dates are ordered
as above (increasing).

 

[Roger Govier]

Hi Peter

take a look at the data>Consolidate function. It will do this for you.
On a new sheet, Highlight columns A and B.
Data>Consolidate>Point to the range you want to consolidate using the
icon at the end of the Reference Pane>Add and repeat for as many ranges
as you wish.
Tick Use labels in Left Column>OK

 

[Guest]

Many thanks Roger. Dim brain on my part.

On closer inspection I notice the date columns have at times been
mis-converted/interpreted on import leading to 11/05 being 05/11 etc with the
result the totals didn't tally.

I am using Excel 2003 and the default import options on convertions -a
closer look at this side of the house needed.

Many thanks
PeterC