Conditional formatting duplicate numbers in a column that also has cells with words - no script

[Merenguito]

Hey guys

I have been looking for this for at least 3-4 hours and I give up. How can I format a column to highlight all duplicate number values, ignoring empty cells or cells with words, without using scripts? I want to use Google Docs with this and if I am not mistaken I can't use scripts. This is what I get: http://prntscr.com/m1ccgs, sadly cells like Yes are also highlighted. I am using this right now: =COUNTIF(L:L,L1)>1

Thank you, and happy holidays!

 

[oldbrewer]

1

yes

3

yes

5

yes

4

no

3

yes

yes

no

21

no

no

no

99

no

no

5

yes

600

no

1

yes

no

no

777

no

yes

no

888

no

no

999

no

formula in F1

=IF(A1="","no",IF(ISERROR(A1*1),"no",IF(SUMPRODUCT(($A$1:$A$19=A1)*1)>1,"yes","no")))

you should be able to build that into one or more conditional formatting equations….happy new year !

<colgroup><col width="64" span="15" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[aqeelnokia99]

=(if(isnumber(a2),sumproduct(--($a$2:$a$20=$a2)),0)>=2)

 

[aqeelnokia99]

=(if(isnumber(a2),sumproduct(--($a$2:$a$20=$a2)),0)>=2)

 

[Merenguito]

Thanks guys, Excel is killing me, I can't make this work. I am no stranger to coding, but Excel looks to defy logic, a simple loop with a couple of ifs seems so difficult in Excel.

The first one I can't make it work at all, I tried changing the pointers so I can steal the start and have a foundation, but to no avail.
I tried this for example changing the first into the second:

=IF(A1="","no",IF(ISERROR(A1*1),"no",IF(SUMPRODUCT(($A$1:$A$19=A1)*1)>1,"yes","no")))
=IF(L1="","no",IF(ISERROR(L1*1),"no",IF(SUMPRODUCT(($L$1:$L$500=L1)*1)>1,"yes","no")))

but it seems to do nothing.

For the second one, I did this:

[COLOR=#333333]=(if(isnumber(a2),sumproduct(--($a$2:$a$20=$a2)),0)>=2)
[/COLOR]into
=(if(isnumber(L2),sumproduct(--($L$2:$L$2000=$L2)),0)>=2)

but it highlights both text cells like None and numbers that don't repeat themselves, while also NOT highlighting duplicates, even if they are on consecutive rows like this: http://prntscr.com/m1ee9s - 9 and 9. 5 is unique, doesn't repeat itself (might it consider 15? ).

Help a desperate man And thanks for the wishes.

 

[Merenguito]

Sorry, I got it wrong! Managed to solve things using =IF(L1="","no",IF(ISERROR(L1*1),"no",IF(SUMPRODUCT(($L$1:$L$500=L1)*1)>1,"yes","no"))), I was using it the wrong way!
Thanks again! And a ho ho ho hoppy new year!