Correlating items from 2 different spreadsheets

[Guest]

Hello,

I have 2 different sheets, 1 for ship A and 1 for ship B.
Both these ships have part numbers and prices associated with them. Lets
say Column A has the part numbers and Column B has the prices associated with
these part numbers. My task is to compare the 2 sheets, find part numbers
that they both have in common and insert the price from Ship B into Ship A's
sheet.

I am thinking that this will be some sort of Macro, but I dont have really
any experience with these. If someone has a different approach, that would
be great.

Thanks

 

[Guest]

Let's consider then that ship B has a lot more prices in it than ship A. And
you want to get prices into ship A.

For Ship A:
Col A Col B
part1 =vlookup(A1,'Ship B'!A:B,2,0)
part2 =vlookup(A2,'Ship B'!A:B,2,0)
etc etc

if it finds the part in ship B, the formula will give you the price for it.
if the part is not found, it will give you #N/A

you can use the tools>filter>autofilter to help you remove all the ugly #N/As

you can reverse the same process to get info from ship A into ship B.

 

[Bernie Deitrick]

I a cell on Ship A's sheet, fill a column of formulas like this

=VLOOKUP(A2,'Ship B'!A:B,2,FALSE)

where A2 has the part number.

Then select that column of formulas, select Edit Go-To Special.... Formulas, Errors, click OK then
press Delete. That will get rid of the formula from rows that don't have a match.

HTH,
Bernie
MS Excel MVP

 

[Guest]

Thats exactly what I was looking for. Thanks for the quick response.

 

[Guest]

Will the vlookup work if the part numbers are not in the same order on both
sheets? i.e., will it search the whole sheet for the part number?

 

[Guest]

Yes, if you use it exactly as I or Bernie have said.
His answer is the same as mine.