Compare & return missing values

[Stephen]

Hi Folks,

I have a sheet named "Compare" with data in columns D, E, G, & H. I would
like to perform a search of the values in Column H begining at row 2 to end,
against the values in column D beginning on row 6 to end, and return row
values from columns G & H where the H value for a given row DOES NOT appear
in column D. I need the returned values to be listed on a second sheet named
"Results" in columns D & E beginning on row 2.

For example if "Compare" H2 has a value of 0062 and that value does not
appear anywhere in "Compare" column D, then copy and paste the values from
Compare" G2 & H2 to "Results" D2 & E2. Then start again with "Compare" H3
against "Compare D and if there is no match in column D, then copy and paste
"Compare" G3 & H3 to "Results D3 & E3.

I'm only looking to copy and paste those rows from "Compare" columns G & H
where there is no match. If there is a match then do nothing and move on to
the next H row on "Compare".

Any help is always greatly appreciated.

TIA!

 

[Joel]

Sub no_match()

ResultsRowcount = 2
CompareRowcount = 2
With Sheets("Compare")
Lastrow = .Range("D" & Rows.Count).End(xlUp).Row
Set D_Range = .Range("D6" & Lastrow)
Do While .Range("H" & CompareRowcount) <> ""
G_Data = .Range("G" & CompareRowcount)
H_Data = .Range("H" & CompareRowcount)
Set c = D_Range.Find(what:=H_Data, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
With Sheets("Results")
.Range("D" & ResultsRowcount) = G_Data
.Range("E" & ResultsRowcount) = H_Data
ResultsRowcount = ResultsRowcount + 1
End With
End If
CompareRowcount = CompareRowcount + 1
Loop
End With

End Sub

 

[Stephen]

Brilliant! That's exactly what I was looking for.

I've just gotta say that some of you guys have a fantastic skill and I, for
one, truly appreciate that you are willing to share your knowledge with the
rest of us.