• 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.

VBA to restrict entering duplicate values in 2 different columns individually

Kiran Kumar

New Member
Hi Team,


Can you please help me to restrict entering duplicate values in A and C column.

For example, if I enter 1(A1 CEll) and 1 again in (A2) then the A2 cell should not accept and should prompt and error messages says - "Duplicate value". Same way for C column also. One more, user can enter same values in both A and C columns, but not in the same colulmn.


Column (A) Column ( C)

1 -----> Should Allow 5 -----> Should Allow

1 -----> Should not allow 1 -----> Should Allow


Thanks much for your help in advance

5 -----> Should not allow
 
Hi, Kiran Kumar!


Select column A or the cell range you want to use, go to Data tab, Data Tools group, Data Validation icon, Configuration tab, Allow Custom option, and in Formula enter this:

=COUNTIF(A:A;A1<=1)


Same for column C.


Regards!
 
Hi,


I know the data validation, but it doesn't work when the user copy and paste the data onto the cell. That's why i prefer to put some VBA code. Can someone help me out as requested earlier.


As usual thanks much for all of your help.


Rgds,

KK
 
Hi, Kiran Kumar!


The suggestion of using data validation, whether or not you were aware of the technique, or despite of your VBA text in the topic title, was intented for making it easier the implementation, as you wrote about "entering" and not "copying" values. Sorry if it wasn't suitable for your job.


The VBA suggestion is this:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim I As Long, rng As Range

Set rng = Application.Intersect(Target, Range("A:A"))

If rng Is Nothing Then Exit Sub

With rng

For I = 1 To .Rows.Count

If Application.WorksheetFunction.CountIf(Range("A:A"), .Cells(I, 1).Value) > 1 Then

Application.Undo

MsgBox "error"

Exit For

End If

Next I

End With

End Sub


Regards!
 
Hi,

Thanks for your response this. But, unfortunately this is not the answer I am looking for...


I do not want to restrics only A column, I want to restrics both A and C columns.

In brief, user should not be able to enter any duplicate values in the A column similarily in the B column also... But user can enter the value in both the column (A & C).


A1 - 1 (Allow)

A2 - 1 (Should not allow)


B1 - 2(Allow)

B2 - 1(Allow)

B3 - 2 (Should not allow)


Hope the above example is easy to understand. Please check this and help me...


Rgds,

KK
 
You could've edited SirJB7's code to suit your requirements.

[pre]
Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Or Target.Column = 3 Then
If Application.CountIf(Columns(Target.Column), Target) > 1 Then
MsgBox "Duplicate Entry Not Allowed!", vbCritical
Target.ClearContents
End If
End If
End Sub
[/pre]
 
Dam Cool:) This is what I am looking for.... You guys are rocking. Hope one day I'll also do macros like this. ha ha ha. Thanks to all of you for your wonderful support on this.


From now on, I'll keep posting and answereing queries.

Rgds,

KK
 
Hi Kiran,


* Please find the attached sheet, and confirm if its working accordingly..


https://dl.dropbox.com/u/78831150/Excel/VBA%20to%20restrict%20entering%20duplicate%20values%20in%202%20different%20columns%20individually%20%28Kiran%20Kumar%29.xlsm


Regards,

Deb
 
Hi there,


Thanks to all of you for the support.


Sorry, was giving more work to the chandoo.org. The macro tunes fine, but have found an observation that, if user copy some 3 values froms sheet2 ->(A1 - 1 B1 - 2 C1 - 3) and pasted in Sheet 1 A1 cell and again he copies the same data from sheet 2 to and pasted in Sheet 1 A2 cell. It accepts eventhough there is duplicate values in A and C.


Pls check if there is any possibility to restrict entering duplicate values like this also.


Sorry once again for too many followups posts.


Rgds,

KK
 
Hi Kiran,


Thank God, you asked the actual question.. not the dummy.. data validation one..

Did you checked my post..?


Regards,

Deb
 
Hi Debraj,


I dont prefer datavalidation, as datavalidation doesn't work when we copy and paste the data onto the datavalidation cell right! So, I prefer a VBA code which will definately restrict eventhough the users copy and paste the data.


Pls check, if it is possible.


Rgds,

KK
 
Modifying the above code a little, this will now loop through all cells modified in change. Note that this will slow things down if you ever try to delete/add a whole bunch of cells at once.

[pre]
Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
If Intersect(Target, Range("A:A")) Is Nothing And _
Intersect(Target, Range("C:C")) Is Nothing Then Exit Sub
For Each c In Target 'in case of multiple cells
If c.Column = 1 Or c.Column = 3 Then
If Application.CountIf(Columns(c.Column), c) > 1 Then
MsgBox "Duplicate Entry Not Allowed!" & vbNewLine & "See cell: " _
& c.Address(rowabsolute:=False, columnabsolute:=False), vbCritical
c.ClearContents
End If
End If
Next c
End Sub
[/pre]
 
Back
Top