Range compare help needed

[jerry203]

Hi all, I need a bit of help. I have been struggling with some code that needs to look for the contents of one range in another range in a separate workbook.
I have been looking at all kinds of sample code but I don't know the 'best practice' approach for what I need to do.

I have two workbooks, DMG and DataExtract, I need to take a range from the sheet "Group" in DMG and search a range in sheet "Data" from DataExtract for any matches. When a match is found I'd like it to copy the row and paste it in the blank sheet "Output" also under the DataExtract workbook. It then needs to carry on looking for matches as "Data" may contain more than one of each value in "Group".

I'm not sure the dictionary object is ideal but it is the rabbit hole I'm stuck in, any help will be greatly appreciated
Regards
Jerry

''Dim Cl As Range

''   Dim Dic As Object
''   Dim NumberRows As Long
''   Dim wbSource As Workbook, wbTarget As Workbook
''   Dim SrcSht As Worksheet, TgtSht As Worksheet
 
''   Application.ScreenUpdating = False
''   Application.Calculation = xlCalculationManual

''   Set Dic = CreateObject("scripting.dictionary")
''   Set wbSource = Workbooks("DMG.xlsm")
''   Set wbTarget = Workbooks("DataExtract.xlsm")
''   Set SrcSht = wbSource.Sheets("Group")
''   Set TgtSht = wbTarget.Sheets("Data")
''   Set Output = wbTarget.Sheets("Output")
  
''   NumberRows = TgtSht.Cells(Rows.Count, 6).End(xlUp).Row ' last row in column F

''   For Each Cl In TgtSht.Range("F2:F" & NumberRows)
''      Dic(Cl.Value) = Empty
''   Next Cl
''   For Each Cl In SrcSht.Range("B2", SrcSht.Range("B" & Rows.Count).End(xlUp))
''      If Dic.Exists(Cl.Value) Then
''         Output.Range("A" & Rows.Count).End(xlUp).Offset(1).Value = Cl.Value
''      End If
''   Next Cl
''   Application.ScreenUpdating = True

 

[Akuini]

@jerry203
Welcome to MrExcel.
1. Could you post some sample data from both sheets? also the result as expected?
2. Data in Sheets("Group") col B, are there any duplicate?

 

[jerry203]

@jerry203
Welcome to MrExcel.
1. Could you post some sample data from both sheets? also the result as expected?
2. Data in Sheets("Group") col B, are there any duplicate?

Thank you.
The data in Sheets("Group") col B is userid's so examples would be smithc, turnerd2, harrisj, jonesp1 etc
There won't be any duplicates in Sheets("Group")
I can't post much sample data from Sheets("Data") as I have to de-identify it but here is a small bit

 

[jerry203]

Would I be better to not use the dictionary object and just have some kind of if/else loop?

 

[Akuini]

I think dictionary object is a good idea.
But I don't quite understand what you're trying to do. Can you explain in more detail?

I need to take a range from the sheet "Group" in DMG and search a range in sheet "Data" from DataExtract for any matches.