Highlight Duplicate Values Post First Occurence

[blackystrat]

Hello

Since I work with a lot of data files that involve names and addresses of local businesses, I find a lot of duplicates in the names, addresses, emails etc.

"Remove Duplicates" doesn't help as it removes all occurrences of a duplicate value, including the first one. I ideally need something that helps me retain the first occurrence of a value and highlight/ remove the subsequent occurrences.

So far I was using this code with Conditional Formatting to highlight values within a column that appeared more than once. However I feel that this code doesn't always give the correct results. Sometimes I find it highlighting unique values as well.

=COUNTIF($A$1:A1,A1)>1

Could you experts please check and advise:-

1. If this code is correct?

2. What values to change in the code if my data starts from Row 2. I can see that the first $A$1 refers to the start of the range, but I don't know if I should leave the other two values to A1 or change them to A2 as well.

3. If there is another and a better way to get this accomplished. i.e. - remove duplicates while retaining the first occurrence


Thank you very much!

 

[JoeMo]

Hello

Since I work with a lot of data files that involve names and addresses of local businesses, I find a lot of duplicates in the names, addresses, emails etc.

"Remove Duplicates" doesn't help as it removes all occurrences of a duplicate value, including the first one. I ideally need something that helps me retain the first occurrence of a value and highlight/ remove the subsequent occurrences.

So far I was using this code with Conditional Formatting to highlight values within a column that appeared more than once. However I feel that this code doesn't always give the correct results. Sometimes I find it highlighting unique values as well.

=COUNTIF($A$1:A1,A1)>1

Could you experts please check and advise:-

1. If this code is correct?

2. What values to change in the code if my data starts from Row 2. I can see that the first $A$1 refers to the start of the range, but I don't know if I should leave the other two values to A1 or change them to A2 as well.

3. If there is another and a better way to get this accomplished. i.e. - remove duplicates while retaining the first occurrence


Thank you very much!

Remove Duplicates does not remove the first occurrence (wouldn't be much point in that) - give it another try.

 

[blackystrat]

Dumb me. You're right. I wonder why and how I got that impression

So could you tell me what that piece of code does then and if it's a correct one to use?

Many thanks

 

[JoeMo]

Dumb me. You're right. I wonder why and how I got that impression

So could you tell me what that piece of code does then and if it's a correct one to use?

Many thanks

What you posted is not " a piece of code", its a worksheet formula. When that formula returns TRUE, the cell its in has a value that is duplicated by another cell above it in column A.