How to put data validation in a column for duplicate values in excel?

Question

I have to put data validation in column "B" for duplicate text values. so users cannot enter duplicate values in column "B".

I used the custom formula for this and it is working fine but it has a limitation. when user copy and paste values then it is not working, it is only working when user enter values by typing into a cell.

My custom formula: =COUNTIF($B$10:$B$1048576,B10)=1

I am expecting a solution that even user cannot enter duplicate values by copy and paste.

Pᴇʜ · Accepted Answer · 2019-07-05 09:13:32Z

1

You can add a Worksheet.Change event to your worksheet and test for duplicates there. If a duplicate was inserted (pasted or typed) then just .Undo the paste.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim AffectedRange As Range
    Set AffectedRange = Intersect(Target, Me.Columns("B"))

    If Not AffectedRange Is Nothing Then  'if at least one cell in column B was changed
        Dim Cell As Range
        For Each Cell In AffectedRange 'loop throuh all changed cells in column B
            If Application.WorksheetFunction.CountIf(Me.Columns("B"), Cell.Value) > 1 Then 'test if it is a duplicate
                Application.Undo 'undo the paste/insert
                MsgBox "Duplicates are not allowed", vbExclamation
                Exit For
            End If
        Next Cell
    End If
End Sub

answered Jul 5, 2019 at 9:13

Pᴇʜ

57.9k10 gold badges55 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sandeep Bhatt Over a year ago

Thanks, Peh I am trying some enhancement in this code. like if my cell has text value like "is StackOverflow is a good website ?" so, in this, I just want to check for text value and leave "?". ex: if in another row in the same column has a text "is StackOverflow is a good website", the code also works in this scenario too.

Pᴇʜ Over a year ago

@SandeepBhatt If this is a question please ask a new one. We can only answer one question at a time.

Collectives™ on Stack Overflow

How to put data validation in a column for duplicate values in excel?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related