another comparison problem

[Timehri]

Here is my problem:
I have two spreadsheets.

Spreadsheet 1 contains approx. 2000 part numbers, and has the headings: item number, description and price.

Spreadsheet 2 contains approx. 300 part numbers and has the headings: item number, vendor number and description.

Is there any way to compare the item number from Spreadsheet 2 with the item number from Spreadsheet 1 and if the item numbers match have the vendor number from Spreadsheet 2 added to Spreadsheet 1 (in a column beside 'item number'), and if they don't match have the item number, vendor number and description added to Spreadsheet 1.

I appreciate any help with regards to this matter.

[ This Message was edited by: Timehri on 2002-04-24 10:23 ]

[jrnyman]

If you can stand a macro, this should work.


Sub CompareParts()
Sheets("SHeet1").Select
Columns("B:B").Insert
Range("B2").Select 'Assuming you have a header row
Do While Not IsEmpty(ActiveCell)
ActiveCell.Formula = "=VLOOKUP(A2,Sheet2!$A$1:$C$10000,2,FALSE)"
ActiveCell.Offset(1,0).Select
Loop
Sheets("Sheet2").Select
LastRow = Range("A65536").End(x1Up).Row
Rows(LastRow & ":" & LastRow).Copy
Sheets("Sheet1").Select
Range("A65536").End(x1Up).Offset(1,0).Select
ActiveSheet.Paste
Range("A1:D10000").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Columns("A:D"), Unique:=True
End Sub

You should note though, that this will simply hide the duplicate lines and not delete them.

[Timehri]

Jrnyman,
I tried the macro, but all it did was insert a blank column. It did not bring over the vendor number from Sheet 2 and insert it into the blank column.

Tim