Forum Discussion
Highlight First Occurrence across mutliple colmuns and rows.
I am having trouble getting my conditional formatting to work. I am trying to highlight the first occurrence of value in a range of D19:I137.
I tried condition formatting duplicate value and it changed all occurrences to the same color. I also tried entering a formula but still can't get it to work.
I would like the first occurrence of a name to be in red and any duplicates in black.
| James | Linda | Peggy | Lisa | Paula |
| Karen | George | Sara | James | Mike |
| Mike | James | Tom | Karen | Carrie |
| Tom | Carrie | Mike | Sam | George |
Any suggestions would be great.
Karen,
Please forget the formula in the previous reply!
Looks like it's not working so well!However, I'll guide you to this rich and helpful article!
When you open it, scroll down to find this subject:
Highlight duplicates in multiple columns without 1st occurrences
But please note that the solution in the article is the opposite of what you want, so please change the operator in the formulas from (>) to (=) to highlight 1st occurrences in multiple columns without duplicates.
I've tested this solution for you, please find the attached file.
Hope that helps.
That is a great article. Thanks for sharing.
Haytham Amairah wrote:
Karen,
Please forget the formula in the previous reply!
Looks like it's not working so well!However, I'll guide you to this rich and helpful article!
When you open it, scroll down to find this subject:
Highlight duplicates in multiple columns without 1st occurrences
But please note that the solution in the article is the opposite of what you want, so please change the operator in the formulas from (>) to (=) to highlight 1st occurrences in multiple columns without duplicates.
I've tested this solution for you, please find the attached file.
Hope that helps.
Hello Karen,
Please use the attached version of workbook. I updated the cell reference and it is working.
Hello Karen,
I believe, I got a solution for you.
please see attached workbook and kindly confirm.
Hi Jamil, your answer is as same as Haytham Amairah's answer.
In your file, try to change F22 and G21 to Tony.
Hello Willy Lau
Can you elaborate how you concluded that my answer is the same as Haytham's?
You are wrong about that "Hi Jamil Mohammad, your answer is as same as Haytham Amairah's answer. "
the formulas I used in the conditional formatting are two formulas.
First one =COUNTIF($D$19:D19,D19)=1 Applied to Range =$D$19:$D$37
Second one =AND(COUNTIF($D$19:$D$37,E19)=0,COUNTIF($E$19:E19,E19)=1) Applied to Range =$E$19:$I$37
Haytham's Formula is this below which has nothing to do with either of the above two formulas I used.
=COUNTIF($D$19:D137,D19)=1
Re : "In your file, try to change F22 and G21 to Tony"
it was just a reference error. that is also fixed in the attached file.
Hi Karen,
Please try this formula,
=COUNTIF($D$19:D137,D19)=1
But make sure to select the whole range D19:I137 so that the cell D19 is the active cell before you apply this formula in the Conditional Formatting.
Your answer highlight the 3rd row Tom.
