CODEWITHSUNDEEP
excelexcel-formuladuplicates
Dale Townsend
Dale Townsend

Reputation: 691

Delete duplicates and keep the topmost value

I have consolidated two columns of data into one column in my spreadsheet, and color-coded them. There are about 300 duplicates in the list.

How would I delete duplicates and keep the topmost value?

Using the Delete Duplicates or Filter > Advanced Filter (with unique records only) removes duplicates from both lists (in the same column). I want the topmost list (which I have colored differently to the bottom one) to stay the same and the bottom list which may have duplicates from the top list to be shortened.

Upvotes: 3

Views: 13587

Answers (1)

Andy G
Andy G

Reputation: 19367

You can highlight the second area and use Remove Duplicates to just remove duplicates from this selected area.

Assuming the first range is A2:A7 and the second range follows this from A8, enter the following formula into B2 and copy all the way down:

IF(AND(ROW(A2)>ROW($A$7),COUNTIF($A$2:$A$7,A2)>0),"remove","keep")

This will ignore the first range ("keep") and indicate "remove" for rows in the second range that occur in the first range. You can then AutoFilter the area and delete the data with "remove".

You could use instead this simpler version of the formula, which just inserts TRUE or FALSE:

=AND(ROW(A2)>ROW($A$7),COUNTIF($A$2:$A$7,A2)>0)

This will remove all duplicates in the second area though. That is, it won't keep the first of the duplicated values. Added actually, if you want to keep the first of these, you could use Remove Duplicates again, based on the second area and both columns' values.

Upvotes: 3

Related Questions