Need Help with 140,000 Records

[KHRS]

Our problem is we have 6 XL spreadsheets that have a total of
140,000 records. Each record has a control number and we need to
combine all the records or be able to look across all the records to
verify that they are within a certain time frame and also delete
duplicates. There are 400 duplicate control numbers that we need to
search for across the 6 spreadsheets.
Let me know if this is not enough info.
Thanks for any of your help.

 

[Earl Kiosterud]

Kris,

You'll be able to do it with Excel 2007, as it has about a million rows.
Excel 2003 and back have only 64K. With those, you'd have to jump through
some hoops to do it -- write a macro probably. You can pull the stuff into
Access easily enough and do it there.

 

[Otto Moehrbach]

Do you have one file with 6 sheets, or 6 files with one sheet each, or 6
files with multiple sheets each?
What do mean by "combine all the records"?
What "certain time frame"?
What do you want to happen if a record is not within the "certain time
frame"?
What constitutes a "duplicate"? Just the control number, or the entire row?
Do you want ALL duplicates deleted?
Is the layout of all the sheets the same? That is, are the headers in the
same row for all sheets? Are the starting rows the same? Columns the same?
Etc?
HTH Otto

 

[David F Cox]

Think about a database, Access would seem ideal.

A properly set up database would not allow you to enter duplicates . (select
no duplicates option)
or a wizard would delete the duplicates for you, or a query would
consolidate the duplicates.

A query would select all of the invalid records, and allow correction.

And when you need Excel, you can just drag and drop a query into a
spreadsheet and your data will be put there.

Think about a database, but do not rush it.

 

[KHRS]

Thanks for all your help and suggestions, we got it worked around with
an access database.

Thanks
Kris

 

[aaron.kempf]

it's not just a 'workaround' it's a superior platform

Excel isn't a tool; it is a toy like solitaire and xbobx and internet
explorer

-Aaron