Delete Row if column has duplicate value
 

I am looking for a way to delete an entire row of data if column B has a
duplicate value (the entire row of data will be different)
In the example below I want to keep the first version of data and delete all
duplicates that appear after.
Delete Row's 5 & 8 (duplicate ID No 99)
Delete Row 7 (duplicate ID No 49)

A B C D
1Junk ID No Junk Junk
2 xxx 99 yyy zzz
3 aaa 49 yyy bbb
4 xxx 65 yyy zzz
5 xyz 99 123 abc
6 xxx 87 yyy zzz
7 xxx 49 yyy zzz
8 123 99 qwe fgh
9


Appreciate any thoughts or suggestions

Thanks!
xjvs

Delete Row if column has duplicate value
 

Hi xjvs,

One way,
Put this into cell E3 and drag down to the end of your data
=IF(COUNTIF($B$2:B3,B3)1,1,"")
This will return a 1 in any row that is a duplicate

Then select A1 to E1 and goto DataAutofilter
On the E1 dropdown select 1
Delete all the filtered rows
Turn Autofilter off and you should be done

HTH
Martin

"xjvs" wrote in message
...
I am looking for a way to delete an entire row of data if column B has a
duplicate value (the entire row of data will be different)
In the example below I want to keep the first version of data and delete
all duplicates that appear after.
Delete Row's 5 & 8 (duplicate ID No 99)
Delete Row 7 (duplicate ID No 49)

A B C D
1Junk ID No Junk Junk
2 xxx 99 yyy zzz
3 aaa 49 yyy bbb
4 xxx 65 yyy zzz
5 xyz 99 123 abc
6 xxx 87 yyy zzz
7 xxx 49 yyy zzz
8 123 99 qwe fgh
9


Appreciate any thoughts or suggestions

Thanks!
xjvs