|
comparing two sheet of data -- cost price increases
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 |
comparing two sheet of data -- cost price increases
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" wrote: 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 |
comparing two sheet of data -- cost price increases
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" wrote: 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" wrote: 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 |
| All times are GMT +1. The time now is 08:05 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com