how do I highlight duplicates cells in excell prior to deletion

P

Paul B

I don't want to delete them straight away as I found that the new remove
duplicates function has removed entries which were not exact duplicates
 
M

Mike H

Paul,

This is very vague. Are we talking about highlighting duplicates in 1 or 2
columns or something else?

Mike
 
P

Paul B

Thanks for replying. Ok, I have a worksheet with 5 columns, one of which
shows mobile phone numbers, the other four show time of day of response, date
message etc. These are records from people who have sent in text entries. A
quick scan shows that a number of people have texted in more than once but
with a different message. I want to be able to highlight these duplicate
entries before I decide whether to delete them or not.

clearer?
 
M

Mike H

Hi,

Select your range then add this conditional format
=COUNTIF($A$1:$A$100,A1)>1
and select a colour

Note that in this Case A1 is the top cell of the range so change to suit.

Mike
 
P

Paul B

Thanks Mike,
this has indeed highlighted some cells, but strangely the numbers
highlighted are not duplicates. There first four digits are the same but the
rest are different. effectively this means that the formula is deleting
numbers with same area code....curious
 
M

Mike H

Try,

=IF(VLOOKUP(A3,Offers!$B$2:$U$751,18,FALSE)=0,"",VLOOKUP(A3,Offers!$B$2:$U$751,18,FALSE))

Mike
 
Top