comparing two sheet of data -- cost price increases

S

SD

I have 3 Sheets of data.
the first on has two columns NEW COST PRICES
SHEET1 A2 TO B9251
A B
PART NUMBER COST



SHEET2 A2:C18051 CURRENT COST AND SELL PRICES
A B C
PART NUMBER COST SELLING PRICE


SHEET3 A2:B20500
A
PART NUMBER

I NEED TO CREATE A FORMULA TO COMPARE THE NEW COST PRICES SHEET1 WITH THE
CURRENT COST PRICES SHEET2 AND SHOW THE PERCENTAGE DIFF.
I NEED TO MATCH THE EXACT PART NUMBER FROM COLUMN A

ON SHEET 3 I NEED TO COMPARE THE PART NUMBER IN COLUMN A AND MATCH IT
EXACTLY TO THE PARTS IN SHEET2 AND SHOW THE COST PRICES (COLUMN B) OR 0 VALUE
IF NO MATCH
 
M

Martin Fishlock

SD: You need to use vlookup for the formula I think oon sheet3

[B2]=>
if(isna(vlookup(a2,sheet2!$a$2:$c$18051,2,false)),0,vlookup(a2,sheet2!$a$2:$c$18051,2,false))
[c2]=>
if(isna(vlookup(a2,sheet1!$a$2:$b$9251,2,false)),0,vlookup(a2,sheet1!$a$2:$b$9251,2,false))
[d2]=>if(b2*c2=0,"",(b2/c2-1)%


then copy the formulas down. note you are unclear were the %inc should sit.
 
M

Martin Fishlock

Sorry, realised there was an error in the formula, there should be a closing
bracket on the end:
[d2]=>if(b2*c2=0,"",(b2/c2-1)%

[d2]=>if(b2*c2=0,"",(b2/c2-1)%)



--
Hope this helps
Martin Fishlock, www.nyfconsultants.com, Bangkok, Thailand
Please do not forget to rate this reply.


Martin Fishlock said:
SD: You need to use vlookup for the formula I think oon sheet3

[B2]=>
if(isna(vlookup(a2,sheet2!$a$2:$c$18051,2,false)),0,vlookup(a2,sheet2!$a$2:$c$18051,2,false))
[c2]=>
if(isna(vlookup(a2,sheet1!$a$2:$b$9251,2,false)),0,vlookup(a2,sheet1!$a$2:$b$9251,2,false))
[d2]=>if(b2*c2=0,"",(b2/c2-1)%


then copy the formulas down. note you are unclear were the %inc should sit.
--
Hope this helps
Martin Fishlock, www.nyfconsultants.com, Bangkok, Thailand
Please do not forget to rate this reply.


SD said:
I have 3 Sheets of data.
the first on has two columns NEW COST PRICES
SHEET1 A2 TO B9251
A B
PART NUMBER COST



SHEET2 A2:C18051 CURRENT COST AND SELL PRICES
A B C
PART NUMBER COST SELLING PRICE


SHEET3 A2:B20500
A
PART NUMBER

I NEED TO CREATE A FORMULA TO COMPARE THE NEW COST PRICES SHEET1 WITH THE
CURRENT COST PRICES SHEET2 AND SHOW THE PERCENTAGE DIFF.
I NEED TO MATCH THE EXACT PART NUMBER FROM COLUMN A

ON SHEET 3 I NEED TO COMPARE THE PART NUMBER IN COLUMN A AND MATCH IT
EXACTLY TO THE PARTS IN SHEET2 AND SHOW THE COST PRICES (COLUMN B) OR 0 VALUE
IF NO MATCH
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top