software

I've got two spreadsheets: our current pricing on products that we get from a
particular vendor and the new pricing for 2006 that they've just sent us.

I think that I may have to put both of these spreadsheets in the same
workbook, as multiple sheets or something.

What I'm trying to do is compare the current pricing and the new pricing to
see where we need to make adjustments. I was hoping to find a way to look at
a particular part number in the first sheet and find that number in the
second sheet, then take the price for that item in the second sheet and enter
it into a new column by that item in the first sheet.

So... the first sheet would have have two columns: part number and current
pricing. The second sheet would have two columns: part number and new
pricing. On the first sheet, I need a third column to bring in the new
pricing from the second sheet.

The final version of the first sheet would show part number, current price
and new price.

Is there a way to do this?

Thank you in advance for any assistance you may be able to provide.

One way is to use VLOOKUP ..
Assume we have
In sheet: Current
Part#PriceNewPrice
111110?
111220?
etc

and, in sheet: New
Part#Price
111115
111225
etc

(quot;Newquot; will house the new prices)

Then in sheet: Current
Put in C2:
=IF(A2=quot;quot;,quot;quot;,VLOOKUP(A2,New!A:B,2,0))
Copy C2 down as far as required

Col C will return the prices from quot;Newquot; for the part# in col A

Perhaps better (but longer) with a dash more error trapping,
we could also put in C2, and copy down:
=IF(A2=quot;quot;,quot;quot;,IF(ISNA(MATCH(A2,New!A:A,0)),
quot;Part# not found in Newquot;,VLOOKUP(A2,New!A:B,2,0)))

The above will return the phrase: quot;Part# not found in Newquot;
for unmatched part#s, if any, instead of quot;uglyquot; #N/As

Adapt to suit ..
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;nacluquot; wrote:

gt; I've got two spreadsheets: our current pricing on products that we get from a
gt; particular vendor and the new pricing for 2006 that they've just sent us.
gt;
gt; I think that I may have to put both of these spreadsheets in the same
gt; workbook, as multiple sheets or something.
gt;
gt; What I'm trying to do is compare the current pricing and the new pricing to
gt; see where we need to make adjustments. I was hoping to find a way to look at
gt; a particular part number in the first sheet and find that number in the
gt; second sheet, then take the price for that item in the second sheet and enter
gt; it into a new column by that item in the first sheet.
gt;
gt; So... the first sheet would have have two columns: part number and current
gt; pricing. The second sheet would have two columns: part number and new
gt; pricing. On the first sheet, I need a third column to bring in the new
gt; pricing from the second sheet.
gt;
gt; The final version of the first sheet would show part number, current price
gt; and new price.
gt;
gt; Is there a way to do this?
gt;
gt; Thank you in advance for any assistance you may be able to provide.

Would this formula still work if the part numbers on each sheet are not in
exactly the same row? They've added some new items to their listing, so they
are not in the same order, at all.

Thanks for your reply, Max!

quot;Maxquot; wrote:

gt; One way is to use VLOOKUP ..
gt; Assume we have
gt; In sheet: Current
gt; Part#PriceNewPrice
gt; 111110?
gt; 111220?
gt; etc
gt;
gt; and, in sheet: New
gt; Part#Price
gt; 111115
gt; 111225
gt; etc
gt;
gt; (quot;Newquot; will house the new prices)
gt;
gt; Then in sheet: Current
gt; Put in C2:
gt; =IF(A2=quot;quot;,quot;quot;,VLOOKUP(A2,New!A:B,2,0))
gt; Copy C2 down as far as required
gt;
gt; Col C will return the prices from quot;Newquot; for the part# in col A
gt;
gt; Perhaps better (but longer) with a dash more error trapping,
gt; we could also put in C2, and copy down:
gt; =IF(A2=quot;quot;,quot;quot;,IF(ISNA(MATCH(A2,New!A:A,0)),
gt; quot;Part# not found in Newquot;,VLOOKUP(A2,New!A:B,2,0)))
gt;
gt; The above will return the phrase: quot;Part# not found in Newquot;
gt; for unmatched part#s, if any, instead of quot;uglyquot; #N/As
gt;
gt; Adapt to suit ..
gt; --
gt; Max
gt; Singapore
gt; savefile.com/projects/236895
gt; xdemechanik
gt; ---
gt; quot;nacluquot; wrote:
gt;
gt; gt; I've got two spreadsheets: our current pricing on products that we get from a
gt; gt; particular vendor and the new pricing for 2006 that they've just sent us.
gt; gt;
gt; gt; I think that I may have to put both of these spreadsheets in the same
gt; gt; workbook, as multiple sheets or something.
gt; gt;
gt; gt; What I'm trying to do is compare the current pricing and the new pricing to
gt; gt; see where we need to make adjustments. I was hoping to find a way to look at
gt; gt; a particular part number in the first sheet and find that number in the
gt; gt; second sheet, then take the price for that item in the second sheet and enter
gt; gt; it into a new column by that item in the first sheet.
gt; gt;
gt; gt; So... the first sheet would have have two columns: part number and current
gt; gt; pricing. The second sheet would have two columns: part number and new
gt; gt; pricing. On the first sheet, I need a third column to bring in the new
gt; gt; pricing from the second sheet.
gt; gt;
gt; gt; The final version of the first sheet would show part number, current price
gt; gt; and new price.
gt; gt;
gt; gt; Is there a way to do this?
gt; gt;
gt; gt; Thank you in advance for any assistance you may be able to provide.

I figured it out, Max.... THANK YOU VERY MUCH!

quot;nacluquot; wrote:

gt; Would this formula still work if the part numbers on each sheet are not in
gt; exactly the same row? They've added some new items to their listing, so they
gt; are not in the same order, at all.
gt;
gt; Thanks for your reply, Max!
gt;
gt; quot;Maxquot; wrote:
gt;
gt; gt; One way is to use VLOOKUP ..
gt; gt; Assume we have
gt; gt; In sheet: Current
gt; gt; Part#PriceNewPrice
gt; gt; 111110?
gt; gt; 111220?
gt; gt; etc
gt; gt;
gt; gt; and, in sheet: New
gt; gt; Part#Price
gt; gt; 111115
gt; gt; 111225
gt; gt; etc
gt; gt;
gt; gt; (quot;Newquot; will house the new prices)
gt; gt;
gt; gt; Then in sheet: Current
gt; gt; Put in C2:
gt; gt; =IF(A2=quot;quot;,quot;quot;,VLOOKUP(A2,New!A:B,2,0))
gt; gt; Copy C2 down as far as required
gt; gt;
gt; gt; Col C will return the prices from quot;Newquot; for the part# in col A
gt; gt;
gt; gt; Perhaps better (but longer) with a dash more error trapping,
gt; gt; we could also put in C2, and copy down:
gt; gt; =IF(A2=quot;quot;,quot;quot;,IF(ISNA(MATCH(A2,New!A:A,0)),
gt; gt; quot;Part# not found in Newquot;,VLOOKUP(A2,New!A:B,2,0)))
gt; gt;
gt; gt; The above will return the phrase: quot;Part# not found in Newquot;
gt; gt; for unmatched part#s, if any, instead of quot;uglyquot; #N/As
gt; gt;
gt; gt; Adapt to suit ..
gt; gt; --
gt; gt; Max
gt; gt; Singapore
gt; gt; savefile.com/projects/236895
gt; gt; xdemechanik
gt; gt; ---
gt; gt; quot;nacluquot; wrote:
gt; gt;
gt; gt; gt; I've got two spreadsheets: our current pricing on products that we get from a
gt; gt; gt; particular vendor and the new pricing for 2006 that they've just sent us.
gt; gt; gt;
gt; gt; gt; I think that I may have to put both of these spreadsheets in the same
gt; gt; gt; workbook, as multiple sheets or something.
gt; gt; gt;
gt; gt; gt; What I'm trying to do is compare the current pricing and the new pricing to
gt; gt; gt; see where we need to make adjustments. I was hoping to find a way to look at
gt; gt; gt; a particular part number in the first sheet and find that number in the
gt; gt; gt; second sheet, then take the price for that item in the second sheet and enter
gt; gt; gt; it into a new column by that item in the first sheet.
gt; gt; gt;
gt; gt; gt; So... the first sheet would have have two columns: part number and current
gt; gt; gt; pricing. The second sheet would have two columns: part number and new
gt; gt; gt; pricing. On the first sheet, I need a third column to bring in the new
gt; gt; gt; pricing from the second sheet.
gt; gt; gt;
gt; gt; gt; The final version of the first sheet would show part number, current price
gt; gt; gt; and new price.
gt; gt; gt;
gt; gt; gt; Is there a way to do this?
gt; gt; gt;
gt; gt; gt; Thank you in advance for any assistance you may be able to provide.

gt; gt; gt; =IF(A2=quot;quot;,quot;quot;,VLOOKUP(A2,New!A:B,2,0))

gt; gt; Would this formula still work if the part numbers
gt; on each sheet are not in exactly the same row?

Yes, of course lt;ggt;

That's the main benefit of using formulas such as VLOOKUP (with 4th param
set to zero for exact matches)

But admit it could have been better illustrated in the example descript
given in the response.
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;nacluquot; gt; wrote in message
news
gt; I figured it out, Max.... THANK YOU VERY MUCH!
gt;
gt; quot;nacluquot; wrote:
gt;
gt; gt; Would this formula still work if the part numbers on each sheet are not
in
gt; gt; exactly the same row? They've added some new items to their listing, so
they
gt; gt; are not in the same order, at all.
gt; gt;
gt; gt; Thanks for your reply, Max!