deleting duplicate entries in Excel 2003

[okie11]

Is there a way to delete multiple duplicate entries in excel 2003. basically what i want to do is tell it to look for any duplicate entries and delete them I have over 20,000 cells and it's taking to long using the find and replace tool. I'm willing to use seperate programs to do this if you have any suggestions

[Hi Ho]

The only thing I can think of is find/replace (Edit > Find) but I'm not sure if it would help or not.

[okie11]

thats what i've been doing but it's taking forever

[Metaphorically]

What I do for those (when it's a one-time sheet) is something like

=(b2==b1)

in column C (assuming the duplicates are in column B, and I've sorted by column B).

Then I copy this all the way to the bottom (ctrl-down arrow goes to the bottom of a contiguous region, ctrl-d will copy the top cell into all selected cells under it). Now column C will contain a bunch of TRUE and FALSE values.

Any row that says "TRUE" in column C has the same value in column B as row above it. You can't sort by column C yet because excel will recalculate and change all the values. So select column C, copy it, then go to Edit -> Paste Special and paste values.

This turns the formulae into the values TRUE or FALSE. Now you can sort by column C and all the rows that have TRUE there are duplicates, so you can delete them.

Otoh, if it's a sheet that I'm going to keep using, I write some VBA.