conditional format to indicate duplicate entry

  • Thread starter Thread starter Todd
  • Start date Start date
T

Todd

Hi there, I am doing a lot of data entry and there are
lots of duplicates. Can I make a conditional format that
could flag me that I have already used a number before, in
the same column?

TIA


Todd
 
Select one cell in your range you'd like to check (in below example, range
is F11:F14, and F11 has been selected).

FORMAT>Conditional Formatting>Formula is:
=COUNTIF(F$11:F$14,F11)>1

don't forget the $
OK

With the cell still selected, press CTRL C
select the entire range (f11:f14), then
EDIT>PASTE SPECIAL>Formats
OK

done
 
Hi

Let's do it for column A on Sheet1
Define a named range
ListToCheck=OFFSET(Sheet1!$A$1,,,ROW(Sheet1!$A1),1)

Select cell A1 selected, and format cell A1 conditionally
Formula is:
=AND(SUMPRODUCT((ListToCheck=A1)*1)>1,ListToCheck<>"")
and set fill color for case the formula returns TRUE.

Copy format of A1 to some range in column A. From now on, whenever you enter
some value twice into formatted range, the cell with 2nd (or 3rd, or 4th, or
....) occurence of non-empty value is colored, until you don't have any gaps
in formatted area.

I wasn't able to find out, why this doesn't work with gaps - the formula
does return the proper value, but somehow the the format doesn't act
properly anymore. Maybe somebody has an idea!
 
Back
Top