Copy this event handler to the clipboard (highlight the entire event handler, right click inside the highlighted area, and 'Copy'):
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
Dim i, j, LastRowO, LastRowN
LastRowO = Sheets("Old").Range("F" & Rows.Count).End(xlUp).Row
LastRowN = Sheets("New").Range("F" & Rows.Count).End(xlUp).Row
Sheets("Compare").Range("A3:Z2000").Cle...
For i = 3 To LastRowO
For j = 3 To LastRowN
If UCase(Sheets("Old").Cells(i, "F").Value) = _
UCase(Sheets("New").Cells(j, "F").Value) Then
If UCase(Sheets("Old").Cells(i, "H").Value) = _
UCase(Sheets("New").Cells(j, "H").Value) Then
If Sheets("Compare").Range("A3").Value = "" Then
Sheets("Old").Cells(i, "F").EntireRow.Copy Destination:= _
Sheets("Compare").Range("A3")
Else
Sheets("Old").Cells(i, "F").EntireRow.Copy Destination:= _
Sheets("Compare").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
End If
End If
Next j
Next i
Target.Offset(1).Select
End Sub
Select worksheet COMPARE and right click the sheet tab at the bottom.
Select 'View Code'.
Paste the event handler into the white editing area to the right (right click inside the area and 'Paste').
Close the VBE (red button - top right).
To extract the matches, simply double click any cell in the COMPARE worksheet. Each double click will create a new, clean list, so if additional data is added over time, it will automatically adjust.
Note; on each call, the event handler will clear the range A3:AZ2000 and extract the new data.
Also, the event handler is not case sensitive, so it will consider RED, Red, and red as the same string.
If you good people would be able to help me, I am trying to compare 2 different sheets in a spreadsheet to find if the data in F3:F1000 in sheet 2 (named: OLD) are also anywhere between F3:F1000 in sheet 3 (named: NEW). If the data matches I then need it to check if the data in H3:H1000 in both sheets also matches. If it still does, I need it to populate the entire row into sheet 1 (named: COMPARE) starting in cell A3. I hope this makes sense, I tried to keep it simple, but I can give more detail if needed. Thanks in advance and I look forward to hearing from anyone willing. Thanks!!!
