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.

 

[Incidental]

Hi Stephen

Another way to do it listed below.

Option Explicit
Dim MyCell, MyRng1, MyRng2 As Range
Dim FoundCell As Range
Dim c As Integer
Private Sub CommandButton1_Click()

Worksheets("Compare").Activate

Set MyRng1 = Range("H2:H" & [G65535].End(xlUp).Row)
Set MyRng2 = Range("D6" & [D65535].End(xlUp).Row)

For Each MyCell In MyRng1

Set FoundCell = MyRng2.Find(What:=MyCell, LookAt:=xlWhole)

If FoundCell Is Nothing Then

c = Worksheets("Results").[D65535].End(xlUp).Row + 1

Worksheets("Results").Cells(c, 4).Value = _
MyCell.Offset(0, -1).Value
Worksheets("Results").Cells(c, 5).Value = _
MyCell.Value

End If

Next MyCell

End Sub

Hope this helps