Comparing dientical part numbers but with different prices

[Martin T]

I have two spreadsheets in the same workbook. One is a download with 6000
part numbers in column A, some of which have prices in column B. The second
sheet has less than 1000 of the same part numbers in column A, with new
prices in column B. I want to compare the two and show only the new prices,
they can be in a new column. I am probably being thick, but I can't work out
how to do it.

Sheet 1
Col A Col B
2121-907 0
2121-908 0
2121-909 0
2121-910 0

Col A Col B
2121-907 1.00
2121-909 1.00
2121-910 2.00

Martin T

 

[Sean Timmons]

=if(iserror(VLOOKUP(A2,sheet2!A:B,2,false)),B2,VLOOKUP(A2,sheet2!A:B,2,false))

basically says lookup the part # in sheet 2 and return the price from column
B. If the part # is not found in sheet 2, then just return the value in the
current sheet's column B. This assumes the formula is placed on sheet 1. If
not, just add the sheet name in front of the A2's.

 

[Martin T]

This almost works. However, it returns the value from sheet 1, coulmn B all
the time. I have tried changing things in the formula, but it always returns
the value in column B, sheet 1.

If I use the formula in sheet 2, it returns the value in column B, sheet 2.

The only thing that I didn't mention, don't know if it would affect
anything, is that the data in Column A, sheet 1 is in sequence, i.e. 0001-001
to 8000-999. In sheet 2 the data in column A starts with 1301-001 up to
8000-900 in number order but with gaps, for instance the number after
1301-001 is 2121-900. Only the part numbers with new prices against them are
in column A in sheet 2.

Almost there, hope that you can fix this, it will save me weeks of manual
checking. I have 7 versions of sheet 2 to compare :-(

Martin T

 

[Martin T]

Think that I have cracked it, tried re-naming the sheets and it worked, just
got to get the decimal places worked out now and I am in clover. Thanks a
million.