Excel Data Consolidation Question

Mr E · 20-06-2003 08:53AM #1

Need some help consolidating excel data. I hope someone out there can help me...

At the very simplest level I have a spreadsheet laid out as follows:

ID, Email Address, Count
1, a@b.com,10
1, a@b.com,5
1, a@b.com,3
2, x@y.com,14
2, x@y.com, 2
2, x@y.com, 7
3, t@u.com, 1
3, t@u.com, 3

I need to consolidate the count column into a total, so the spreadsheet would look like this:

ID, Email Address, Count
1, a@b.com, 18
2, x@y.com, 23
3, t@u.com, 4

Whats the easiest way of doing this? Bear in mind that there is alot more data than this, and there are almost 50,000 records....

- Dave.

Capt'n Midnight · 20-06-2003 04:29PM

data - sort -subtotal - at each change in email address use function SUM (rather than count )

well that's how you do it in www.openoffice.org
it's a 54MB download once Vs. €600 per user to get M$ office std ...
(and it opens corrupt excel sheets and you can tell it to always make backup copies when saving all types of files)

Excel - tip - File - SAVE AS - look in the options and select "always create backup copy" - all other excel files will still not save backup copies..

Mr E · 20-06-2003 04:50PM

Tried that.... I ran out of rows. There are about 59,000 rows in the spreadsheet (probably 50,000+ unique addresses), so I hit the 65535 row limit....

- Dave.

3D · 25-06-2003 11:35PM

I you dont have to do it in excel then import it into access and do it simply using a query

Excel Data Consolidation Question

Comments