Restrict entering duplicate data in multiple column

[amruthubballi]

I want to restrict entering duplicate data in multiple column.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]Client Name[/TD]
[TD]Year[/TD]
[TD]Quarter[/TD]
[TD]Form Type[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]ABC[/TD]
[TD]2017-18[/TD]
[TD]Q1[/TD]
[TD]26Q[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]XYZ[/TD]
[TD]2017-18[/TD]
[TD]Q1[/TD]
[TD]26Q[/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]ABC[/TD]
[TD]2017-18[/TD]
[TD]Q1[/TD]
[TD]24Q[/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]ABC[/TD]
[TD]2017-18[/TD]
[TD]Q1[/TD]
[TD]26Q[/TD]
[/TR]
</tbody>[/TABLE]

From the above table we can see that Row no’s 21, 22 & 23 are unique. In Row no’s 21 & 23 are almost same but they differ in form type hence they are also unique. Where as in Row no’s 21 & 24 they are exact same in all column so basically I want a VBA code which should restrict from entering same data twice in Table. If there is a change in one column is acceptable but it cannot be same in all column.

I have got a following code to restrict entering data in only one column

Private Sub Worksheet_Change(ByVal Target As Range)
If Application.CountIf(Range("B21:e120"), Target) > 1 Then
MsgBox "Dulipate Data!", vbCritical, "Remove Data"
Target.Value = ""
End If
End Sub

Please make necessary adjustment to restrict entering same data in Column B to F
I HOPE SOME ONE WILL HELP ME TO RESOLVE THIS
Thanks in Advance.

 

[MickG]

Try this:-
This code should delete any entered value in column "B:F" that turn that row into a duplicate row.

Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("B2"), Range("B" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
Application.EnableEvents = False
[COLOR="Navy"]If[/COLOR] Not Intersect(Target, Columns("B:F")) [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
   [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    Txt = Join(Application.Transpose(Application.Transpose(Dn.Resize(, 5))), ",")
        [COLOR="Navy"]If[/COLOR] Not .Exists(Txt) [COLOR="Navy"]Then[/COLOR]
            .Add Txt, Nothing
        [COLOR="Navy"]Else[/COLOR]
            Target.ClearContents
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]End[/COLOR] If
Application.EnableEvents = True
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[amruthubballi]

Hello Mick

First of all thank you so much for reply.... In your replied code it has some mistakes, it is clearing anything I write in row B:E.

I have attached link here so Please can you or anyone who read this can rectify it and send?

https://drive.google.com/file/d/1Sk_SyMdduNgp8kw5X421C9S-6zC6ayZt/view?usp=sharing


Regards,
Amrut

 

[MickG]

Please provide an explanation of how you propose the code should work in relation to your file sheet data

 

[amruthubballi]

Hi Mick,

Thank you again for reply… I hope you have seen the sheet I have sent…I am entering data by row wise. First I would enter client name, then year, quarter and then form type and rest of the details. In other words, I would enter by row no 21 complete all the details in that row then I would go for row no 22,23 so on as follows.
[TABLE="width: 500"]
<tbody>[TR]
[TD]

[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]20
[/TD]
[TD]Client Name
[/TD]
[TD]Year
[/TD]
[TD]Quarter
[/TD]
[TD]Form Type
[/TD]
[/TR]
[TR]
[TD]21
[/TD]
[TD]ABC
[/TD]
[TD]2017-18
[/TD]
[TD]Q1
[/TD]
[TD]26Q
[/TD]
[/TR]
[TR]
[TD]22
[/TD]
[TD]XYZ
[/TD]
[TD]2017-18
[/TD]
[TD]Q1
[/TD]
[TD]26Q
[/TD]
[/TR]
[TR]
[TD]23
[/TD]
[TD]ABC
[/TD]
[TD]2017-18
[/TD]
[TD]Q1
[/TD]
[TD]24Q
[/TD]
[/TR]
[TR]
[TD]24
[/TD]
[TD]ABC
[/TD]
[TD]2017-18
[/TD]
[TD]Q1
[/TD]
[TD]26Q
[/TD]
[/TR]
</tbody>[/TABLE]

From column B:E relates to a particular event. I am considering all the four columns as a one event which is supposed to be unique in table. I want a VBA code which should restrict from entering same data twice in column B:E

Eg 1: In row 21 & row 22 are completely different as they client name is different hence it is unique data.

Eg 2: In row 21 & row 23 are having same name, year and quarter but they have form type different hence still it is unique data.

But

Eg 3: In row 21 & row 24 are having same name, year and quarter and same form type as well so it is a duplicate data I would enter in the table.

So I want some code which should restrict from entering the same data twice in the table. In other words when I finish entering column E, VBA code should check the above data, if it is duplicate data I should display a message box as warning duplicate data and delete the data I just entered.

I hope this will help to understand what type of code I exactly want…please help!!

Thanks,
Amrut

 

[MickG]

Try this code, it now start from row 21 and also provides a "MsgBox" when a duplicate is found.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range, Dn As Range, Txt As String
Set Rng = Range(Range("B21"), Range("B" & Rows.Count).End(xlUp))
With CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
Application.EnableEvents = False
If Not Intersect(Target, Columns("B:E")) Is Nothing Then
   For Each Dn In Rng
    Txt = Join(Application.Transpose(Application.Transpose(Dn.Resize(, 5))), ",")
        If Not .Exists(Txt) Then
            .Add Txt, Nothing
        Else
            Target.ClearContents
        MsgBox "Duplicate Line !!"
        End If
    Next
End If
Application.EnableEvents = True
End With
End Sub

 

[amruthubballi]

Thank you so much.... It works perfectly as I wanted.

 

[MickG]

You're welcome