Excel VBA to Identify Duplicates from one Worksheet Based on Cell Value from Another Worksheet

[rarascon]

Hi there:

I'm seeing some similar requests and code, and I've been trying to accomplish what I need with either one. However, I'm not quite there or get errors.

My apologies that my code below is a skeleton and not organized very well. I was trying a couple of options, and I've not added any error handling or the usual 'with application' blocks yet.
I'm hoping to use the selected cell value from SourceSht and evaluate for dupes from the table from DID_Employee sheet. When it does find dupes, the user will see a message using the information below alerting them that there are dupes and to select the correct EEID. There may be a better solution for that as well.

I appreciate any assistance!
Rob R

Private Sub TestForDuplicates()
'Determine if duplicate values exist in a provided range and alert user to select the correct EEID

Dim rng1 As Range, frng As Range
Dim myArray As Variant
Dim x As Integer
Dim SourceSht As Worksheet, ws2 As Worksheet
Dim lR1 As Integer, lR2 As Integer

Set SourceSht = ThisWorkbook.Worksheets("Exceptions_HRCases") 'Sheet with source value
Set ws2 = ThisWorkbook.Worksheets("DID_Employee") 'Sheet to find dupes
'Set ws2 = ActiveSheet

    With SourceSht
        .Range("E2").End(xlDown).Select
    End With
        
    With ws2
        lR2 = .Cells(.Rows.Count, 2).End(xlUp).Row
    End With

Set rng1 = SourceSht.Selection

'Use UNIQUE Function and store result to an Array variable
    myArray = WorksheetFunction.Unique(rng1)
    
'Test Range for Duplicates with COUNTIFS Function  *** THIS DOES NOT WORK but I would like to use something similar using the array from ws2.  ***
  For x = LBound(myArray) To UBound(myArray)
    If WorksheetFunction.countifs(rng1, myArray(x,1))> 1 Then
      MsgBox "Found more than one value of " & Chr(34) & myArray(1, x) & Chr(34) & " in the cell range. Please confirm EEID."
      Exit Sub
    End If
  Next x

End Sub

 

[DanteAmor]

Try a formula in the "Exceptions_HRCases" sheet

Dante Amor
	A				E	F	G
1					VALUES		Dups
2					Value1
3					Value2		Found more than one value
4					Value3
5					Value4		Found more than one value
6					Value5
7					Value6		Found more than one value
Exceptions_HRCases

Cell Formulas
Range	Formula
G2:G7	G2	=IF(COUNTIF(DID_Employee!B:B,Exceptions_HRCases!E2)>1,"Found more than one value","")

Dante Amor
	A	B
1		Values
2		Value1
3		Value2
4		Value3
5		Value4
6		Value5
7		Value6
8		Value2
9		Value4
10		Value6
DID_Employee

 

[rarascon]

Try a formula in the "Exceptions_HRCases" sheet

Dante Amor
	A				E	F	G
1					VALUES		Dups
2					Value1
3					Value2		Found more than one value
4					Value3
5					Value4		Found more than one value
6					Value5
7					Value6		Found more than one value
Exceptions_HRCases

Cell Formulas
Range	Formula
G2:G7	G2	=IF(COUNTIF(DID_Employee!B:B,Exceptions_HRCases!E2)>1,"Found more than one value","")

Dante Amor
	A	B
1		Values
2		Value1
3		Value2
4		Value3
5		Value4
6		Value5
7		Value6
8		Value2
9		Value4
10		Value6
DID_Employee

Thank you, Dante! That would be the easier option as I have no problem setting up formulas; however, in this case this is a front-end user form where they need to be alerted when there are more than one Employee with the same name for the name that they are entering. I'm looking for an automated solution. = )

 

[rarascon]

Try a formula in the "Exceptions_HRCases" sheet

Hey, @DanteAmor, I'm flexible! So, I used your solution and it's working now. I added some helper columns in the workbook and added this code with the other Worksheet_Change automated tasks. Works great now. Thank you!

    If Target.Offset(0, xOffsetColumn2).Value > "" Then
    Debug.Print Target.Value
        MsgBox "There is more than one value of " & Chr(34) & Target.Value & Chr(34) & " in the Employee table. Please confirm EEID and CC."

    End If