Parts List comparison report

[Guest]

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!

 

[Nigel]

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

 

[Guest]

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

 

[Guest]

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