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.
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
2️⃣ In the second solution, we used Excel COUNTIF formula to warn for duplicate value entering.
↪️ 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:
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.
The application steps and result of the formula can be seen :