Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto number for a parts list. | Excel Discussion (Misc queries) | |||
Formula needed for my parts list | Excel Discussion (Misc queries) | |||
Printing selected parts of a list.... | Excel Worksheet Functions | |||
cost comparison report | Charts and Charting in Excel | |||
Dependant parts list -lookup | Excel Discussion (Misc queries) |