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!

Parts List comparison report
 

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" wrote in message
...
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!

Parts List comparison report
 

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!

Parts List comparison report
 

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" wrote in message
...
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!