Correlating items from 2 different spreadsheets
 

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

Correlating items from 2 different spreadsheets
 

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 toolsfilterautofilter to help you remove all the ugly #N/As

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


"Reebis" wrote:

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

Correlating items from 2 different spreadsheets
 

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


"Reebis" wrote in message
...
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

Correlating items from 2 different spreadsheets
 

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

Correlating items from 2 different spreadsheets
 

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?

Correlating items from 2 different spreadsheets
 

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

--
Allllen


"Reebis" wrote:

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?