Highlight First Occurrence across mutliple colmuns and rows.

[Karen Heinrich]

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.

[#2]

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.

[#3]

Your answer highlight the 3rd row Tom.

[#4]

Hello Karen,

I believe, I got a solution for you.

please see attached workbook and kindly confirm.

[#5]

Hi @Jamil Mohammad, your answer is as same as @Haytham Amairah's answer.  

In your file, try to change F22 and G21 to Tony.

[#6]

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.

[#7]

Yes, I know.  I mean your second formula does not handle a case that the first occurance is former to the current column but with a latter row ( sorry for my poor English).

[#8]

it is ok. I am not native English either :)

Thanks for clarifying.

Re: "In your file, try to change F22 and G21 to Tony"

that was just a reference error. I fixed it in the attached file.

[#9]

Hello Karen,

Please use the attached version of workbook. I updated the cell reference and it is working.

[#10]

Yes.  It is the reference problem.  I just elaborated how I concluded that your answer is the same as Haytham's.  

[#11]

so, if i understood correctly ;) you are saying now my answer was not the same as Haytham's? right?

[#12]

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 1^st 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 1^st occurrences in multiple columns without duplicates.

I've tested this solution for you, please find the attached file.

Hope that helps.

[#13]

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 1^st 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 1^st occurrences in multiple columns without duplicates.

I've tested this solution for you, please find the attached file.

Hope that helps.

---