Find if a number already exists in column

[dinkss]

Hi All,

I'm trying to get formula or macro to check if number already exists in a column G:G.
If an employee will enter the same number I would like to have a pop up message (Alert) that number entered already exists.

 

[dinkss]

Hi All,

I'm trying to get formula or macro to check if number already exists in a column G:G.
If an employee will enter the same number I would like to have a pop up message (Alert) that number entered already exists.

I'm using xlookup to enter the number in G column but it doesn't show duplicated values

 

[Joe4]

How about using Conditional Formatting to highligh duplicated values?
If you select Column G, and use the following Conditional Formatting formula:

=COUNTIF(G:G,G1)>1

and choose your desired formatting color, it will highlight any duplicated values.

 

[dinkss]

How about using Conditional Formatting to highligh duplicated values?
If you select Column G, and use the following Conditional Formatting formula:

=COUNTIF(G:G,G1)>1

and choose your desired formatting color, it will highlight any duplicated values.

That works great but how can I get pop up message when employee will try to enter value that already exists in columns G?
Basically how can I get excel to show an error message when using xlookup (employee enters text in F column, and xlookup enters number in G column - so I need an error message if the same number will appear in G column).

Is this possible?

 

[Joe4]

OK, I think you probably need VBA to do that.
Right-click on the tab sheet name at the bottom of the screen, select "View Code", and paste this code in the VB Editor window that pops up:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    Dim r As Long
   
'   Set entry range to look at
    Set rng = Intersect(Target, Range("F:F"))
   
'   Exit if no cells updated in watched range
    If rng Is Nothing Then Exit Sub
   
'   Check updated cell for duplicates
    For Each cell In rng
'       Get row of updated cell
        r = cell.Row
'       Check to see if value populated in column G already exists in column G
        If Application.WorksheetFunction.CountIf(Range("G:G"), Range("G" & r)) > 1 Then
            MsgBox "Entry in column F of row " & r & " causes a duplicate in column G", vbOKOnly, "ENTRY ERROR!"
'           ***UNCOMMENT THE FOLLOWING 3 LINES IF YOU WANT TO CLEAR COLUMN F VALUE***
            'Application.EnableEvents = False
            'cell.Clear
            'Application.EnableEvents = True
        End If
    Next cell
   
End Sub

Now, if any manual entry you make in column F causes a duplicate to occur in column G, it will give you an error message.

If you want it to also automatically remove the value from column F that you just entered that caused the duplicate value in column G, uncomment these three lines in the code by removing the single-quote mark that is at the front of each line:

            'Application.EnableEvents = False
            'cell.Clear
            'Application.EnableEvents = True

 

[dinkss]

OK, I think you probably need VBA to do that.
Right-click on the tab sheet name at the bottom of the screen, select "View Code", and paste this code in the VB Editor window that pops up:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    Dim r As Long
  
'   Set entry range to look at
    Set rng = Intersect(Target, Range("F:F"))
  
'   Exit if no cells updated in watched range
    If rng Is Nothing Then Exit Sub
  
'   Check updated cell for duplicates
    For Each cell In rng
'       Get row of updated cell
        r = cell.Row
'       Check to see if value populated in column G already exists in column G
        If Application.WorksheetFunction.CountIf(Range("G:G"), Range("G" & r)) > 1 Then
            MsgBox "Entry in column F of row " & r & " causes a duplicate in column G", vbOKOnly, "ENTRY ERROR!"
'           ***UNCOMMENT THE FOLLOWING 3 LINES IF YOU WANT TO CLEAR COLUMN F VALUE***
            'Application.EnableEvents = False
            'cell.Clear
            'Application.EnableEvents = True
        End If
    Next cell
  
End Sub

Now, if any manual entry you make in column F causes a duplicate to occur in column G, it will give you an error message.

If you want it to also automatically remove the value from column F that you just entered that caused the duplicate value in column G, uncomment these three lines in the code by removing the single-quote mark that is at the front of each line:

            'Application.EnableEvents = False
            'cell.Clear
            'Application.EnableEvents = True

This works fantastic! You are amazing! Thank you very much!

 

[Joe4]

You are welcome.
Glad I was able to help!

 

[dinkss]

You are welcome.
Glad I was able to help!

Hi Joe4 again,

Just wondering would it be possible to add to above script option to highlight cell in yellow if duplicate is found?

Can something like this be added:
cell.Interior.Color = vbYellow

 

[dinkss]

Hi Joe4 again,

Just wondering would it be possible to add to above script option to highlight cell in yellow if duplicate is found?

Can something like this be added:
cell.Interior.Color = vbYellow

It is working I just checked Unless there's other way of doing it?

 

[Joe4]

It is working I just checked Unless there's other way of doing it?

You could just leave the code "as-is" and just use the Conditional Formatting I showed you in my initial posts to highlight the duplicates yellow.
The advantage to using Conditional Formatting is if you fix the issue, the color will go away automatically. Otherwise, you would need to add more code to your VBA code to "undo" the highlighting if the duplicate is fixed.