Check the number of unique cells in a range

Question

I have an excel sheet.

Under column E, I have 425 cells with data. I want to check if the same data (i.e. text inside the cell) is repeated anywhere else in any of the remaining 424 cells under column E. How do I do this?

For example, in E54 I have
Hello Jack

How would I check this value to see if it was in any other of these cells?

Solution 1

You could use

=SUMPRODUCT(1/COUNTIF(E1:E425,E1:E425))

to count the number of unique cells in E1:425

An answer of 425 means all the values are unique.
An answer of 421 means 4 values are duplicates of other value(s)

Solution 2

Use Conditional Formatting on all the cells that will highlight based on this formula:

COUNTIF(E:E,E1) <> 1

This is based on the column being E, and starting on E1, modify otherwise.

In Excel 2010 it's even easier, just go into Conditional Formatting and choose

Format only unique or duplicate values

Comments

[Siddharth Rout]

+ 1 This exactly does what the OP wants.

[Lance Roberts]

Yep, but just getting a count won't help you track them down either. Of course, there are many ways to do it, but I'm used to scrolling looking for highlighting.

[Lou]

+1 Why is this not the accepted answer? Worked for me too for what I wanted to do.

[xtian]

+1, I was not able to implement this in LibreOffice with the argument (E:E,E1) or as a conditional format (very likely OE); however, I did get a variation to work quite nicely as an extra column, =IF(COUNTIF($G$2:$G$725,G2)<>1,1,0)

[bob]

I know that this is late but why does this work? countif returns 0 so 1/countif = #error and sumproduct should fail. breaking the formula into parts, this is exactly what happens.

[brettdj]

@bob the COUNTIF can never return zero as the list is looking up itself - i.e. each value has to occur at least once.