Highlight Duplicate Values Post First Occurence

blackystrat

New Member
Joined
Aug 5, 2014
Messages
26
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.

Code:
=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!
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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.

Code:
=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.
 
Upvote 0
Dumb me. You're right. I wonder why and how I got that impression :confused:

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

Many thanks
 
Upvote 0
Dumb me. You're right. I wonder why and how I got that impression :confused:

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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top