• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Highlighting a cell to represent duplicates

C

CLoos

New Member
Is there a formula that could highlight the first cell in a group of cells if there are repeats? He does not want a macro. A colleague asked me this question and I have not found a solution with Custom Formatting.
 

Attachments

  • example.xlsx
    8.3 KB · Views: 5
neeraj2050 said:
@john: Could you please explain the formula? It's great!!
Click to expand...
It's rather simple... CF works when a formula returns TRUE.
=(COUNTIF(A$2:A2;A2)=1)*(COUNTIF(A$2:A$23;A2)>1)

The first part checks if the number appears for the first time in the range so far (so from the start A$2 until the "active cell"; notice the relative reference :A2 in the second element of the range). But then all numbers do appear at least once when they are in the list. The second parts checks if the number is repeated in the full range A$2:A$23.
Both results get then multiplied.
Meaning you get TRUE * FALSE = FALSE ( 1 * 0 = 0 ) when the number only appears once. And TRUE * TRUE = TRUE ( 1 * 1 = 1 ) when the number appears for the first time, but is also repeated. Only then the CF kicks in.
 
You must log in or register to reply here.
Back
Top