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!

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" wrote:

> 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!

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.

"Joel" wrote:

>
> 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" wrote:
>
> > 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!