Hi,
I am working on finding duplicates in an excel file that contains 545000 rows.
I have tried the conditional formatting method, it runs for more than an hour.
Now I am trying with Countif and this also is taking more than an hour.


Is there a quicker way to find duplicates in a large excel file?

What is considered a duplicate ?
- all cells of one row matching all cells of another row ?
- matching cells in one particular column ?
- something else ?

Perhaps the data can be loaded into an array to be dealt with.
Can you attach a small de-sensitized sample worksheet of what you're dealing with, maybe 100 rows or so ?



PS: Excel 2003 has max of 65,536 rows, you might want to change your profile to be what you actually have.

Maybe you start with reason, why you want those duplicate rows to be marked.

Do you want to delete abundant ones (a reasonable action IMHO)? Then a logical solution will be ODBC query with DISTINCT clause to get all unique entries into a separate table (the amount of rows in original table taken into account, a separate workbook will be reasonable solution). After this you can scrap the original one, or delete the sheet with original table and then move the new table into old workbook.