Large scale summation using Excel for Mac

Hello.

I have to take approximately 1,000,000 records, that break down to approximately 300,000 subsets based on one key field, and for each subset I need to produce a single record in the same format with a sum for that key.

In short, I need to condense the million records with multiple lines per key to a set with one record per key.

Here are the "catches":
- I'm using Excel for Mac, so no VBA.
- Also for the reason noted above, I can't use Access.

(It's a Mac shop. Guess where I work.)

I did this exercise the hard way once, wherein I used the Subtotal function to get the totals by key, and constructed my consolidated record-set off to the right in the same spreadsheet, did a copy-paste value on the sheet, deleted the columns with the detail records, and sorted the blank rows out of my manually created recordset.

This however, took a long time because although the new Excel with 1,048,576 can contain the records in a single sheet, things lock up for a very long time if you try to subtotal even 100,000 records.

Any advice or tips would be greatly appreciated.

Regards,
SAP2112

Have you tried a Pivot Table, or is it just too much data? Pivot Tables automatically collect data by certain fields, so you just would have to pick the keys.