Duplicate Entries

[Millington]

Can anyone let me know how to create a conditional format that can change the font color from the default color to a different color, if I enter duplicate values?

I entered “$10,000 Cash Giveaway” in cells A3 and A11 in the attached page. How can I create a rule, so the font color would change from violet to orange (for example), if I enter duplicate values?

Formatting In Order To Detect Duplicate Entries.jpg

[anon73440385]

Hello

the idea for such a solution is based on

• Conditional formatting
• Searching for the ROW of first occurence (using MATCH function)
• Evaluating the ROW of current input
• Comparing ROW of first occurence and current ROW

To achieve your goal do the following:
(This decription assumes: Start in colum A, Row 2 and input ends in Row 1000)

• Define a cell style fitting your need (in my example below it is called RedBG = Red Background)
• Select range of cells, which you use as input and you want to be “Find duplicates enabled”
• Go to Format -> Conditional -> Condition
• Select “Fomula is” and add MATCH(A2,A$2:A$1000,0)<>(ROW(A2)-1) and Select your previously defined Cell Style (Note: If your input range starts in another row you need to adapt the offset “-1” in (ROW(A2)-offset)
• Select the Cell Range (example: A2:A1000)
• Click OK

[SM_Riga]

There is duplicate condition state under Format -> Conditional Formatting -> Condition, just select Cell value is and duplicate This will apply selected style to all cells with duplicate contents within the range. This is preferable way as it is faster and less resource consuming.

[anon73440385]

Thanks - never noticed that. This is definitely easier to implement and faster.

[erAck]

I suggest @SM_Riga adds his/her comment as answer so it can be upvoted on and marked as correct.