Data Validation for Duplicate Entry

[vijaysahil]

Good Morning !

can anyone solve this issue...

Sr.No SUN
10 A
12 C
10 B
12 D
10 A
12

Here is the question that if i put "A" in front of 10 and again if i put 'A' in front of 10. how it will stop me as showing "Duplicate Data".
I hope you all understand my problem. Please give solution ASAP.

Thank you !...

[AliGW]

Welcome to the forum!

I have improved your title to bring it into line with our requirements (please see our rules).

[vijaysahil]

Thank you for reply
Please resolve the issue.

[avk]

>Select the entire column where you need to avoid duplicates.
>On the Data tab, in the Data Tools group, click Data Validation.
> In the Allow list, click Custom.
> In the Formula box, enter the formula shown below and click OK.

Please Login or Register  to view this content.

>Switch to the "Error alert" tab, and enter your text into the fields Title and Error message.
>In Title mentioned : "Duplicate entry"
In Message Box mentioned : "You have entered an Data that already exists in this column."

[vijaysahil]

Thank you for your reply,
But my concern is if i put same value in front of same Sr.No then it show "Duplicate entry" .
Dupblicate entry.png

[AliGW]

That's precisely what AVK's solution does - did you try it?

[vijaysahil]

Yes i tried.. it showing Duplicate value even i put "A" with other Sr. No..like....10,12....

[AliGW]

Try this:

=AND(COUNTIF(A:A,A2)=1,COUNTIF(B:B,B2)=1)

[vijaysahil]

it's not working.. every entry shows as "duplicate entry"Dupblicate entry.jpg

[samba_ravi]

Try below in B2, Copy and paste towards down
=COUNTIFS($A:$A,$A2,$B:$B,$B2)=1

[vijaysahil]

Thank you so much ..It's Working now....

[samba_ravi]

Always it is better to attach a sample excel file with expected result along with your requirement and explanation

[avk]

Select range i.e. "B2" to "B100"
> In the Allow list, click Custom.
> In the Formula box, enter the formula shown below

Please Login or Register  to view this content.

>Switch to the "Error alert" tab, and enter your text into the fields Title and Error message.
>In Title mentioned : "Duplicate entry"
In Message Box mentioned : "You have entered an Data that already exists in this column."
and click OK.