How do I auto highlight a cell with the same data as another cell

[john talbot]

I have a spreadsheet containing lists of serial numbers. If I enter a serial
number which already exists in the spreadsheet, I would like the new cell to
be highlighted to indicate that the entry already exists - is this possible?

 

[Stefi]

If numbers are in column A then apply conditional formatting to column A with
this formula:

=COUNTIF(A:A,A1)>1

and assign a pattern to it!

Regards,
Stefi

„john talbot†ezt írta:

 

[Khoshravan]

I applied your formula but it didn't work. Have you tried yourself?

 

[John]

Hi
I tried it and it works this way:
Hi started by selecting the range, first cell A1 and then copied the formula
in CF,
I selected a pattern and its working.
HTH
John
both cells are coloured.

 

[Khoshravan]

I understand my problem. The sequence of steps in Stefi's solution is
important. You have to define the formula for one cell and then drag the
formula down the column.
In my way, first I select the range and then applied the formula and it
doesn't work.
The solution was nice.

 

[Khoshravan]

Could you please explain what is the functionality of COUNTIF(A:A,A1)>1? I
know it gives true if the numbers are repeated and give false if they are not
equal, but I can't figure out how does it work.

 

[Pete_UK]

It counts the number of A1 values that are in column A - if there are
more than 1 this means there are duplicates. As the formula is applied
to the second row the A1 becomes A2, so it counts the number of A2
values in column A. Similarly down the column.

Hope this helps.

Pete

 

[Khoshravan]

Thanks. It was very informative and easy to understand. NG is valuable for
these discussions which could not be found in any MS help menus.

 

[Pete_UK]

Well, thanks for feeding back. As I spend a lot of my time on these
NGs, I must agree with you !! <bg>

Pete

 

[Chatnoir11]

This is brilliant! I have a huge database of customer info and this makes it
so simple to find the dupliates!!! Thank you!!

 

[Khoshravan]

May I ask you, how much time do you spend in these NG in average?
I know/asked from these Ng's for a long time but recently have got keen
interest on them to master my skills as much as I can. I spend hours but I
think in case of people like you, it must be more than hours?!?

 

[Pete_UK]

Well, I don't like to tot up the time I spend here - it might make me
feel guilty about not spending the time decorating or on other chores
that my wife would like me to do !! <bg>

Like all things, the more you practise the better you become - I got a
banjo for my birthday recently, so I hope to devote some time to
learning that (when I'm not on here).

Pete