Extract data after comparison

F

fl

I am comparing two spreadsheets that contain 9 columns of data. I want
extract the data if they match in columns 1, 2, 8 and 9 (keys fields).
How do I do this? Thanks.

I normally do this in MS Access. I am learning Excel, and I have
learned a bunch from this newsgroup. Thanks again.
 
N

Norman Jones

Hi FZL,

To extract the common data to a new worksheet, try:

Sub Tester()
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Dim LastCell As Range
Dim i As Long

Set ws1 = ActiveWorkbook.Sheets("Sheet1") '<<< CHANGE
Set ws2 = ActiveWorkbook.Sheets("Sheet2") '<<< CHANGE
Set ws3 = ActiveWorkbook.Worksheets.Add(after:=ws2)
ws3.Name = "Extracted Data" '<<< CHANGE

For i = 1 To ws1.UsedRange.Rows.Count
If ws1.Cells(i, 1) = ws2.Cells(i, 1) Then
If ws1.Cells(i, 2) = ws2.Cells(i, 2) Then
If ws1.Cells(i, 8) = ws2.Cells(i, 8) Then
If ws1.Cells(i, 9) = ws2.Cells(i, 9) Then
Set LastCell = ws3.Cells(Rows.Count,
"A").End(xlUp)(2)
ws1.Cells(1).Resize(1, 9).Copy LastCell
End If
End If
End If
End If
Next i

End Sub

To copy the data to an existing worksheet, replace the lines:

Set ws3 = ActiveWorkbook.Worksheets.Add(after:=ws2)
ws3.Name = "Extracted Data"

with

Set ws3 = ActiveWorkbook.Sheets("YOUR EXISTING SHEET NAME")
 
Top