asked on

MS Excel duplicate input detect.

Hello.
I need an Excel solution for followinf task.
I have 3 colums.
Colum A have a list of numbers whtch is need to detect MATCH when I input a number on ccolum C.
Colum B have a list of numbers whtch is need to detect NO MATCH when I input a number on ccolum C.

Is it possible?
If possible, I want to know how?
I know Excel Data Validation methord but it can only detect MATCH of a colum.

Thank you very much!!

Koen

put this in your validation formula (custom validation):
=AND(COUNTIF(A1:A8;C2)<1;COUNTIF(B2:B6;C2)=1)

Robert Kang

ASKER

Hello.
I tried but gave me an error.
I select A,B, C colums and tried to input to custom.

Koen

you might want to replace the ; by , in the formula (my regional settings are doing that)
and the validation goes on the input cells (so in your picture the column C)

Robert Kang

ASKER

Hello. Koen.
I have to insert a space to apply.
Anyway, It is not workring for me.
Thank you very much.

ASKER CERTIFIED SOLUTION

Koen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Subodh Tiwari (Neeraj)

Select whole column C and put this custom data validation rule and see if that works for you.

=AND(ISNA(MATCH(C1,A:A,0)),ISNUMBER(MATCH(C1,B:B,0)))

Open in new window

Robert Kang

ASKER

Good morning Mr. Koen
I really appreaciate for your sample file and it is working perfect for me.
I think, I need post more question for custom wav or mid(sound) file play when Error Alert windows pop up(#1 sound) and after enter new value in (#2 sound) in colum C.
I am scanning barcodes in a noise place and somtimes I can not hear windows sound very well.
Thank you

Robert Kang

ASKER

Hello. Subodh Tiwari (Neeraj)
I tried but not working.
But, I really appreaciate for your help.
Thank you.