Duplicate check and delete for thousands of records

Hello;
I hope someone can offer a solution or can please point me in the right direction.
We receive huge Excel files and we need to validate for and delete duplicate records before they are imported to a software product called Exceed Premier. We are having a difficult time with Excel spreadsheet duplicate record validation because we have to first export the files from the Exceed database into an Excel spread, merge the thousands of other new records from multiple Excel files, then import back into Exceed.
Is there a method in Excel that can merge several worksheets and check for and delete duplicate irecords? The records will be in the 20-50K range and growing.
Can anyone offer an overall solution to what we are trying to accomplish, or can you please point me toward another method?

Thank you in advance for your help/suggestions.

Paul

My suggestion is that you use Access instead of Excel.

Excel is an excellent analytical tool, but what you need is pure database functionality.

If you don't like that advice, then try using COUNTIF function, auto filter for values >1 and delete visible cells.

Thanks. I like the idea of Access because of the shear volume of records. Can you please tell me if I can import multiple Excel files (they will have all column headings the same per file), run a dup check, and export them out as .CSV files for future import into Excel?

I appreciate your time

please tell me if I can import multiple Excel files (they will have all column headings the same per file), run a dup check, and export them out as .CSV files for future import into Excel?

Yes. The first decision I think you want to make is ... do you have a single "cockpit" for this process or have separate "controls" in Access vs. Excel. It can be done either way.

Next is importing multiple files. I assume the files are all in a single folder in a directory somewhere. If the file names never change, you can keep a list of the file names in a table (either in Access or Excel, depending on decision above) and "loop" through the list of file names. If the file names do change, then you need some logic to be able to pick out the files you want to import from all of the files in a particular folder. Then, "loop" through all of the file names in the directory and import those that fit that criteria.

The most typical way of avoiding overlapping/duplicate data is to use a combination of a temporary table and a permanent table. The permanent table has one field defined as a key index field (this might be the concatentation of several fields in the raw data). So, the process in this case is:
(1) delete all records from the table (but retain the table def itself)
(2) import each file to the temporary table
(3) use an append query to more the records from the temporary table to the permanent table; the key indexed field excludes any duplicate records from entering the permanent table
(4) repeat steps 2 & 3 for each file
(5) export the table in XLS format ... assuming that the number of records is not too great; if the number of records is too large for a single Excel worksheet, then you would break up the data into Excel-sized chunks

If the data need to be broken up, you have two choices: export multiple files, or export a single file with multiple sheets. The former is much easier to do than the latter if your process (up to this point) is entirely in Access; but, it is still quite possible to do it this way by Automating Excel via Access.

thank you for your help