Correlating items from 2 different spreadsheets

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Reebis - 25 Oct 2006 16:43 GMT

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

Reply to this Message

Allllen - 25 Oct 2006 17:06 GMT

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.

Signature

Allllen

> Hello,
>
[quoted text clipped - 10 lines]
>
> Thanks

Reply to this Message

Reebis - 25 Oct 2006 17:27 GMT

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

Reply to this Message

Bernie Deitrick - 25 Oct 2006 17:16 GMT

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

> Hello,
>
[quoted text clipped - 10 lines]
>
> Thanks

Reply to this Message

Reebis - 25 Oct 2006 17:33 GMT

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?

Reply to this Message

Allllen - 25 Oct 2006 17:55 GMT

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

Signature

Allllen

> 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?

Reply to this Message