how can i tell if i have ented the same numbers twice in a column

[Guest]

I enter orders we receive for our business. I list them down in a column, is
there anyway I can tell if I have entered one more than once without having
to go back and check every time?

 

[Bob Phillips]

You could use conditional formatting, with a formula of say
=COUNTIF(B:B,B1)>1. This will then highlight them both as you enter it.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[Dav]

If you apply a conditional format to your column your can make duplicat
entires stand out, so as soon as the second value is entered they wil
both change colour

If your data is in column A try the following in cell a2

=COUNTIF(A:A,a2)>1 entered under formula is in conditional formatin
and select a colour to highlight it

This can then be copied and pasted as formats to the other cells

If it does not work, check that excel has not enclosed the formula i
quotes, if it has delete them!

Regards

Da

 

[Guest]

thank you that is a great idea i will try that

 

[Guest]

thank you that has helped

 

[Guest]

i'm sorry i tried that and it wouldn't work correctly

 

[Guest]

i'm sorry i tried and it wouldn't work correctly

 

[Bob Phillips]

Do want to expand on that? What did happen?

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[Guest]

FALSE popped up I put the correct letters in place of the ones you had in
there and it still didn't respnded correctly. Here is an example of what the
numbers look like that i key in 239089
694185
276784 All of which are keyed in column D

 

[Bob Phillips]

I suggested adding it in Conditional Formatting, not in a worksheet. To do
so, select your column (say column B), goto Format>Conditional Formatting,
change Condition 1 to Formula Is, and add that formula. Click Format, select
Pattern, choose a colour, then OK.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[Peo Sjoblom]

Why not apply validation so it will tell you when you enter a number more
than once?

http://www.cpearson.com/excel/NoDupEntry.htm

try to read the instructions carefully this time, the suggestions you
received previously all work but you need to follow them, conditional
formatting is applied via format>conditional formatting, not by putting the
formulas in cells


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com