3

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.

Improve this question

1 Answer 1

Reset to default
3

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

OK thanks, I understand up to the AutoFilter bit. Which area do I autofilter?
"Which area do I autofilter?" I don't understand: the area that contains the word "remove" (or TRUE, if you use the 2nd formula).

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.