How do I stop repeat entry of a number in the same Excel column

[John]

I am trying to prepare a new sales order management spreadsheet for my
Warehouse staff to record orders reaching them for picking and packing.
Nothing fancy but I have a definiative requirement in stopping orders of a
five digit format from being entered twice unless it is a back-fill order
where we can have a forward slash with a number or letter reference added too.

 

[Sandy Mann]

John,

Try Conditional Formatting using a formula.. You can use it to highlight
duplicates in (say) Column A with the formula:

=COUNTIF($A:$A,$A1)>1

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[John]

Hi Sandy

Thanks for the response. Sadly, Im not as good as I thought and can't make
this formula work in conditioning formatting? I'm using column D in the
spreadsheet where I am trying to prevent two of the same number being
entered. Actual cells are D4 to D250 after column headers etc. Currently,
others headers turn red and bold as soon as any entry is made in column D4
downwards, the conditioning formatting applies but does not indicate a
dublicate entry??

Any further thoughts please???

 

[Sandy Mann]

Hi John,

Follow me through:

Select D4250
With the range still selected, select Format > Conditional Formatting >
Formula Is
then type in:
=COUNTIF($D$4:$D$250,$D4)>1
then select the highlighting colour that you want.

I then only get highlighted cell when I have duplictes.

Post back if you still can't get it to work.


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[John]

Will do Sandy, I am sitting here now as I need to complete this for Tuesday
morning but dont want to commit the whole bank holiday weekend to it! If I
can resolve this now so much the better as we're off out later this evening
too!

Thanks

Rgds John

 

[John]

OK! cracked it.

I have it turning red and everything on a double entry. Thank you. Unable
to make an error warning to appear though just the cells turning red.

Any thoughts??

John

 

[Gord Dibben]

John

If you want a warning or restriction, you must use Data Validation.

See Chip Pearson's site for preventing duplicates.

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


Gord Dibben MS Excel MVP

 

[Sandy Mann]

For an error message you could use Data Validation as well:

Select the range as before and then:

Data > Validation > Settings > Allow = Custom > Formula
=SUMPRODUCT(--(EXACT(D4,$D$4:$D$250)))=1

then > Error Alert and enter the message you want.

The error message will appear and the other duplicate will be highlighted

The EXACT function is needed because Validation found part numbers or other
Order numbers.


An alternative is to enter in E4:

=IF(COUNTIF($D$4:$D$250,$D$4:$D$250)>1,"Double Order Number","")

and copy down to E250

Or to have only one cell giving the error message leave it in E4


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk