Kapat

Show Warning If Duplicate Value Entered In Column Cells

excel  vba sample Prevent Duplicate Values
In Excel Column

We created two separate the solution ways ,to prevent duplicate data entry into the column and to show a warning .

1️⃣ In the first solution, we used VBA codes. While entering data, if there are duplicate values in column A ,with a msgbox is warned the user . Msgbox shows the repeated data in the column A with the row number .If user want, can enter this the repeated value or can delete it.
In the VBE window, we added the following codes to the Worksheet_Change procedure of the worksheet. excel show warning duplicate value

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim son As Long, onay, bul As String
    Dim ara As Range
    
    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    
    son = Cells(Rows.Count, "A").End(xlUp).Row
   On Error Resume Next
    If WorksheetFunction.CountIf(Range("A2:A" & son), Target) < 1 Then
    Exit Sub
    End If
    If WorksheetFunction.CountIf(Range("A2:A" & son), Target) > 1 Then
        
    bul = Empty
    Set ara = Range("A2:A" & son).Find(Target, , xlValues, xlWhole)
    If Not ara Is Nothing Then
     adres = ara.Address
       Do
     bul = bul & ara.Row & "      -      " & Cells(ara.Row, "A") & Chr(10)
    Set ara = Range("A2:A" & son).FindNext(ara)
    Loop While Not ara Is Nothing And ara.Address <> adres
    End If
    onay = MsgBox("Row :        Records :" & vbCrLf & Chr(10) & bul & vbLf & "Do you want to enter?", vbYesNo, "")
    If onay = vbYes Then MsgBox "Recording has been completed.", vbInformation, "Info"
    If onay = vbNo Then Target.ClearContents
    End If
End Sub
excel warning for duplicate values

2️⃣ In the second solution, we used Excel COUNTIF formula to warn for duplicate value entering. excel countif formula
↪️ First of all, on the Sheet2 sheet, we selected column E.
↪️ We have selected Custom item in Allow section of Data Validation_Settings tab from “Data” top menu. In the Formula section, we entered this formula:
=COUNTIF(E:E;E1)=1
excel countif formula for duplicate values
Since the entire column E is selected, Excel will spread the formula across all column cells.
↪️ We have entered the warning text in the Title and Error Message sections of the Error Alert tab. The Stop option should remain in the Style section.
vba countif

The application steps and result of the formula can be seen : excel warning if value is already exists

Prevent duplicate entries in Excel

📥 Download the workbook that contains two sample sheets.