• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data validation for WO column

aligahk06

Member
Dear All,

In COL A cell i have number values for eg.
A2 987542156
A3 687512157
A4 887542159
and so on.........

I want if same value entered in next cell for eg A5 then it flash a message duplicate already entered.

How could i acomplish the task
 

Attachments

pecoflyer

Active Member
Select your range starting starting in A2 to A562
Click Insert - Table - Check " ...headers" - Ok
Now select starting form A3 to the end of the range
Select Data - data validation - Select "Custom" - Enter =COUNTIF($A$3:$A3,$A3)=1 as formula
In the "Enter alert" tab enter what you want - OK
Now when you enter a duplicate value a message pops up
 

Attachments

aligahk06

Member
aligahk06
... flash?
or something like this way
... You could see - where have You already used those values.
Sir,
Could you please help me in understanding the formula youused in chk Column.
=IF(COUNTIF([Work Order],[@[Work Order]])>1,"DUP","") How does it works Please elaborate ?

I used data validation option through custom formula
COUNTIF($A$3:$A3,$A3)=1 to flash duplicate records.
 

vletm

Excel Ninja
aligahk06
Did You read my the last line?
Did You check that my file?
There were already few duplicates - marked with "DUP". Those Work Order -values has uses more than once.
With that DataValidation, You can see if that Work Order -value has used after You have written it (... but not where? ... not already duplicates )
Formula is basic same as in DataValidation - [Work Order] is as A-column from table as well as @[Work Order] is that rows Work Order-value.
What else would You know?
Check Countif-function eg from below
 

aligahk06

Member
aligahk06
Did You read my the last line?
Did You check that my file?
There were already few duplicates - marked with "DUP". Those Work Order -values has uses more than once.
With that DataValidation, You can see if that Work Order -value has used after You have written it (... but not where? ... not already duplicates )
Formula is basic same as in DataValidation - [Work Order] is as A-column from table as well as @[Work Order] is that rows Work Order-value.
What else would You know?
Check Countif-function eg from below
Sir,
Got it.

Thank you..
 
Top