Delete both duplicate on a list

[usc1000]

I am currently using Excel 2003 and have a list over 1000 unique id numbers. I know how to delete duplicate numbers, but I want to be able to find duplicate id numbers on the "B" column and delete both records for good. Any help would great!

 

[usc1000]

Any help would be very appreciated. Thanks

 

[Gerald Higgins]

Hi usc1000, welcome to the board.

There are several ways to do this.
One way, use a helper column to identify the duplicates using a formula like this -

=if(countif(b:b,b1)>1,1,0)

copied all the way down.

This will put a 1 next to any item that appears more than once, and a 0 next to any item that appears only once.
Then use data, filter, to display only the rows with a 1 in the helper column, and delete them.

 

[usc1000]

Thank you, Sir. I will try this, but it might take a while for me since i am not very good with excel formulas

 

[Gerald Higgins]

OK. Good luck. Post back if you need more help.

 

[usc1000]

Hi usc1000, welcome to the board.

There are several ways to do this.
One way, use a helper column to identify the duplicates using a formula like this -

=if(countif(b:b,b1)>1,1,0)

copied all the way down.

This will put a 1 next to any item that appears more than once, and a 0 next to any item that appears only once.
Then use data, filter, to display only the rows with a 1 in the helper column, and delete them.

I try it and it works very well. Thank you! I am going to try to create a macro to make it easier to use.