Highlighting Duplicates When Values Are Text

[mrsjbw]

I have downloaded evaluation data into Excel and I have instances where the same person took the evaluation twice. I want to highlight those instances to call attention to them. The problem is this: my columns of data don't offer enough unique data to differentiate. The most I can come up with is that I have a column that lists the question # and a column that lists the evaluee's name. I'm trying to come up with a COUNTIF formula that says "if the number in the Question column=1, AND if the evaluee's name occurs > 1 time, count it"


Any advice? I know my problem is that I can't figure out how to count the name text as a value... I think....


THANKS!! Jesse

 

[fred]

countif ($a$:a1,a:a)

 

[fashinefa]

Burberry totes bags

Great post. I found what I wanted. I recommend you to visit our Web Design London blog, and share ideas. Also, thanks for allowing me to comment!

 

[NARAYANK991]

Hi Jesse ,


Can you try the following formula in a helper column ?


Say your data starts from A2 , and goes down till B37 ; the question numbers are in column A , and the candidates' names are in column B.


In cell C2 type in the following formula :


=COUNTIFS($B$2:$B$45,B2,$A$2:$A$45,A2)


Copy this down till C37.


Whichever combination of question and candidate is a duplicate , will have a number greater than 1 ( depending on how many times it is repeated ) , while singletons will have 1 , in the cells C2 through C37.


Based on this , if you want , you can apply conditional formatting to the cells A2 through B37 ; have the formula =C2>1 in the rule for conditional formatting , so that the duplicate cells will display in Red.


Narayan