Delete Row if column has duplicate value

X

xjvs

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
 
M

MartinW

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 Data>Autofilter
On the E1 dropdown select 1
Delete all the filtered rows
Turn Autofilter off and you should be done

HTH
Martin
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top