| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Tracking Cost Price from Different Sheets | Excel Discussion (Misc queries) | |||
| Cost to Retail Price Formula - some help pse | Excel Worksheet Functions | |||
| Converting cost price to retail in vast array | Excel Worksheet Functions | |||
| annual increases in price lists | Excel Discussion (Misc queries) | |||
| formula for cost as percentage of sale price | New Users to Excel | |||
Linear Mode
