Parts List comparison report

I have a list of 3942 parts w/ descriptions and pricing information. I have
used a series of If(NA(Vlookup)) formulas to scrub this list down to 3,533
part numbers that are not currently in our system. I would now like to have
the all the information from the larger list (descriptions, pricing, terms,
etc) reduced to the 3,533. How can I best accomplish this?

basically if a part number from sheet 1 A2 matches a part number from the
parts column of worksheet 2, I would like sheet 3 Row 2 to print the entire
row from Worksheet 2 that matched the partnumber from sheet 1 A2. Is there a
way to do this?

Thanks!

Why do you not use a Vlookup function in the 3,533 list to get the data from
the larger list?

--
Cheers
Nigel



"DA@PD" <DAPD@discussions.microsoft.com> wrote in message
news:B008D4B2-A1F1-4DDF-8AE8-5C4B87A23EBA@microsoft.com...
> I have a list of 3942 parts w/ descriptions and pricing information. I
have
> used a series of If(NA(Vlookup)) formulas to scrub this list down to 3,533
> part numbers that are not currently in our system. I would now like to
have
> the all the information from the larger list (descriptions, pricing,
terms,
> etc) reduced to the 3,533. How can I best accomplish this?
>
> basically if a part number from sheet 1 A2 matches a part number from the
> parts column of worksheet 2, I would like sheet 3 Row 2 to print the
entire
> row from Worksheet 2 that matched the partnumber from sheet 1 A2. Is
there a
> way to do this?
>
> Thanks!

Hi,
Try this (on test data first!).

It matches Sheet1, Col A vs Sheet2, Col A and if match found, COPIES (not
Prints) Sheet2 data to Sheet3. Change colums as required (as I wasn't sure of
column for part number in sheet2).

HTH


Sub GetPartsData()

Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Dim outrng As Range
Dim lastrow As Long, r As Long

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
Set ws3 = Worksheets("Sheet3")
Set outrng = ws3.Range("A2")

With ws2
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
Set rng = .Range("A2:A" & lastrow) ' Set list of Sheet2 part numbers
from Col A (?)
End With

ws1.activate

With ws1
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
For r = 2 To lastrow ' loop through column A of Sheet1
res = Application.Match(.Cells(r, "a"), rng, 0) ' find match
If Not IsError(res) Then ' Match found
ws2.Rows(res + 1).EntireRow.Copy outrng ' Copy Sheet2 data to
Sheet3
Set outrng = outrng.Offset(1, 0)
End If
Next r
End With

End Sub

"DA@PD" wrote:

> I have a list of 3942 parts w/ descriptions and pricing information. I have
> used a series of If(NA(Vlookup)) formulas to scrub this list down to 3,533
> part numbers that are not currently in our system. I would now like to have
> the all the information from the larger list (descriptions, pricing, terms,
> etc) reduced to the 3,533. How can I best accomplish this?
>
> basically if a part number from sheet 1 A2 matches a part number from the
> parts column of worksheet 2, I would like sheet 3 Row 2 to print the entire
> row from Worksheet 2 that matched the partnumber from sheet 1 A2. Is there a
> way to do this?
>
> Thanks!

Thats what I'd like to do, but unfortunately am not sure how to pull data
from other columns at the same time. I know how to "match" with the parts
column, but really don't know how to use vlookup to get the data from the
other columns.

thanks,

David

"Nigel" wrote:

> Why do you not use a Vlookup function in the 3,533 list to get the data from
> the larger list?
>
> --
> Cheers
> Nigel
>
>
>
> "DA@PD" <DAPD@discussions.microsoft.com> wrote in message
> news:B008D4B2-A1F1-4DDF-8AE8-5C4B87A23EBA@microsoft.com...
> > I have a list of 3942 parts w/ descriptions and pricing information. I
> have
> > used a series of If(NA(Vlookup)) formulas to scrub this list down to 3,533
> > part numbers that are not currently in our system. I would now like to
> have
> > the all the information from the larger list (descriptions, pricing,
> terms,
> > etc) reduced to the 3,533. How can I best accomplish this?
> >
> > basically if a part number from sheet 1 A2 matches a part number from the
> > parts column of worksheet 2, I would like sheet 3 Row 2 to print the
> entire
> > row from Worksheet 2 that matched the partnumber from sheet 1 A2. Is
> there a
> > way to do this?
> >
> > Thanks!
>
>
>