Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete row where there is duplicate data in Column E | New Users to Excel | |||
delete duplicate record but only determine 1 column data | Excel Worksheet Functions | |||
delete duplicate names in a column | New Users to Excel | |||
Is there a quick way to delete all duplicate rows in a column? | Excel Discussion (Misc queries) | |||
I want to delete rows with duplicate entries within one column. | Excel Discussion (Misc queries) |