1

In a full sheet range, I need a cell to be highlighted when a duplicate is typed, whether it is a number (phone number in this case) or a text string (name in my case). Both the new cell and the cell it is duplicating need to be highlighted.

I've tried the "countif" formula but to no avail. If it matters, I'm in a French version of Sheets.

  • So, a duplicate may appear anywhere in the sheet, not necessarily in the same column as the original value? Also, are both values (original and duplicate) to be highlighted? – user79865 Jul 27 '16 at 11:58
  • yes, yes, this is exactly what I want to do! cheers – izzy Jul 27 '16 at 13:34
  • @izzy: Please note that comments are meant to be temporary. Important information should be incorporated into the post. I've added this information to your post, but please make sure you do it next time some asks for clarification. – ale Jul 27 '16 at 20:22
2

This formula should do the trick. Assumes your range begins at a1 and ends at a100. Adjust as needed.

=AND(COUNTIF($A$1:$E$100,"="&A1)>1,not(isblank(A1)))

  • @ Did you take into account the comment by @words "a duplicate may appear anywhere in the sheet, not necessarily in the same column as the original value? Also, are both values (original and duplicate) to be highlighted?" ? Are you sure the code provided will work for the whole sheet as is? – marikamitsos Jul 27 '16 at 18:14
  • The OP specifically said that both are to be highlighted in his comment on his own post. As for the disjoint range, (and possibility that some cells are empty), I've updated the answer to make it clearer how to do that. – Steve Jul 27 '16 at 19:56
  • Hey Steve! thanks for your swift return but sorry, it does not work.....I tried tweaking it until I lost hope....it says "invalid formula"; did you take into account I'm on a French version of Sheets (unless it does not make any difference). Alas I have to leave on vacations in 2 hours and if nobody gives me a hint, I'll have to wait until my return. Thanks anyway! – izzy Jul 28 '16 at 9:19
  • Not sure if you use any french functions, but bettercloud.com/monitor/the-academy/… explains how to use english function names, otherwise, not sure I can help since I only know the English function names – Steve Aug 1 '16 at 16:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.