Detect if a cell is duplicate

**Jinxedcookie** · 11-25-2011, 06:18 PM

(Sorry, not sure how to describe in a line)

I have a mass of data in Columns A through T.
I would like to find duplicates in Column A that only have negative numbers in Column T and highlight them (all duplicates) with Red Cells. (See AndrewLynas below).
Then I would like to highlight all the single cells in A that have negative numbers in T with Orange Cells
Then I would like to find duplicates in Column A and if they are a duplicate and also have a negative number in Column T highlight them Blue, providing there is a duplicate with a positive number in Column T.
All cells In Column T that are positive numbers should be uncoloured.
At the end of it I am looking to sort the colours, so I can skim off the data for other uses

Column A Column T
AlistairWilliams 109
AlistairWilliams -2
AlistairWilliams -177
[COLOR="rgb(255, 140, 0)"]AlonitStefanini -55[/COLOR]
AlunLethbridge 4
AlunLethbridge -202
AlunLethbridge -328
AlunLethbridge -405
AlunLethbridge -573
AndrewBeer 17
AndrewBeer -102
AndrewBeer -158
AndrewBeer -340
AndrewLycett 54
AndrewLycett -128
AndrewLynas -70
AndrewLynas -224
AndrewSesemann 50
AndyBarnard 26
AndyBradley 41
AndyEllis -220
AndyWatkins -358
AndyWatkins -603

I hope that's clear! Thanks in advance for all your help, oh and I don't mind if it is multiple steps, but would you please give an explanation of the solution

**darkyam** · 11-25-2011, 06:59 PM

There are two ways to do this.
1. You can have a helper column with this formula: =IF(T2>0,"Black",IF(COUNTIF($A$2:$A$23,$A2)>1,IF(SUMPRODUCT(MAX(($T$2:$T$23)*($A$2:$A$23=$A2)))>=1,"Blue","Red"),"Orange")), and then make very simple conditional formatting formulas off the helper column colors, such as =$U2="Orange" and then just hide U.

2. You can have three conditional formatting formulas. For blue, =AND($T2<0,COUNTIF($A$2:$A$23,$A2)>1,SUMPRODUCT(MAX(($T$2:$T$23)*($A$2:$A$23=$A2)))>=1). For Red, =AND($T2<0,COUNTIF($A$2:$A$23,$A2)>1,SUMPRODUCT(MAX(($T$2:$T$23)*($A$2:$A$23=$A2)))<=0). For Orange, =AND(COUNTIF($A$2:$A$23,$A2)=1,$T2<0)