Conditional Formatting for Triple+ Values in Column

[JEH105]

Hello and help!

I am trying to set up a conditional formatting formula that highlights similar values in the same column (Column U) listed more than 2 times. The issue is that I need the formula to only match the text before the "/" character, since the number will always be unique.

Example: ABC Business, Inc. / #12345678

I tried this formula, but I'm guessing I'm doing something wrong since it didn't work:

=COUNTIF(LEFT($U2,SEARCH("/",$U2)),$U2)>2

Any help is appreciated, thank you!

 

[arthurbr]

I think you need to add the entire range
=COUNTIF(LEFT($U2,SEARCH("/",$U2),$U2:$U100)>2

 

[JEH105]

I think you need to add the entire range
=COUNTIF(LEFT($U2,SEARCH("/",$U2),$U2:$U100)>2

Thank you, I keep getting the attached error with this formula.

 

[Fluff]

How about

=COUNTIF($U$2:$U$100,LEFT($U2,SEARCH(" ",$U2))&"*")>2

 

[JEH105]

How about

=COUNTIF($U$2:$U$100,LEFT($U2,SEARCH(" ",$U2))&"*")>2

Thank you, but this didn't work either. It's highlighting values that are not listed more than three times. I think it's probably because of the change in the formula from "/" to " "?

 

[Georgiboy]

Hello and help!

I am trying to set up a conditional formatting formula that highlights similar values in the same column (Column U) listed more than 2 times.

Might be the last bold bit in the above quote that brought that about, try changing the >2 to >3 in @Fluff's formula

***Ignore me then, I need to learn to look further than the end of my nose soemtimes!

 

[Fluff]

I think it's probably because of the change in the formula from "/" to " "?

Yup I changed that to test it & forgot to change it back.

 

[JEH105]

Yup I changed that to test it & forgot to change it back.

Yes! Thank you so much, this worked!

However, I'm realizing I need this CF for multiple sheets. Where if the value is listed more than 2 times in column U of multiple sheets, the value highlights. I tried this one, but it didn't work:

=COUNTIFS($U$2:$U$500,LEFT($U2,SEARCH("/",$U2))&"*">2,
$U$2:$U$500,LEFT('Sheet2'!$U2,SEARCH("/",'Sheet2'!$U2))&"*">2)