Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
deleting products
I have a list of products with a corresponding trade value. e.g: Product Code Trade Value (£) 01 100 02 245 03 453 04 546 05 6756 06 980 07 2435 08 346356 09 4235 10 45 11 476 12 356 13 3566 14 588 Now i also have a list of product Exceptions-these are 6-digits long, but the first 2-digits correspond to the above numbers. e.g: Product Val(£) 012657 10 013458 5 014456 6 024565 3 025455 12 034345 20 and so on. I want to create a new list that subtracts the 6-digit values from the 2 digit values. So for product 01 the calculation would be 100-10-5-6=79. The new table would look like: Product Val 01 79 02 230 03 433 etc Is there a formula to do this, thanks for the help. Chris -- cj21 ------------------------------------------------------------------------ cj21's Profile: http://www.excelforum.com/member.php...o&userid=25673 View this thread: http://www.excelforum.com/showthread...hreadid=508126 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
deleting products
Assuming the 1st table (product code-trade value) is in Sheet1, the 2nd
table (product exceptions) is in Sheet2, both within cols A & B, data from row2 down (The Prod code & Product values in both sheets are assumed text numbers) In the sheet for the 3rd table Product Val 01 79 02 230 03 433 To compute the "Val" in the table: Put in B2: =VLOOKUP(A2,Sheet1!A:B,2)- SUMPRODUCT(--(LEFT(Sheet2!$A$2:$A$100,2)=A2),Sheet2!$B$2:$B$100 ) Copy B2 down Adapt the ranges to suit: Sheet2!$A$2:$A$100 Sheet2!$B$2:$B$100 (Note that we can't use entire col refs in SUMPRODUCT) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "cj21" wrote in message ... I have a list of products with a corresponding trade value. e.g: Product Code Trade Value (£) 01 100 02 245 03 453 04 546 05 6756 06 980 07 2435 08 346356 09 4235 10 45 11 476 12 356 13 3566 14 588 Now i also have a list of product Exceptions-these are 6-digits long, but the first 2-digits correspond to the above numbers. e.g: Product Val(£) 012657 10 013458 5 014456 6 024565 3 025455 12 034345 20 and so on. I want to create a new list that subtracts the 6-digit values from the 2 digit values. So for product 01 the calculation would be 100-10-5-6=79. The new table would look like: Product Val 01 79 02 230 03 433 etc Is there a formula to do this, thanks for the help. Chris -- cj21 ------------------------------------------------------------------------ cj21's Profile: http://www.excelforum.com/member.php...o&userid=25673 View this thread: http://www.excelforum.com/showthread...hreadid=508126 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
deleting products
Max Any chance you could post an example. Thanks for the help Chris -- cj21 ------------------------------------------------------------------------ cj21's Profile: http://www.excelforum.com/member.php...o&userid=25673 View this thread: http://www.excelforum.com/showthread...hreadid=508126 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
deleting products
Slight tweak to the VLOOKUP part ..
(think its better to search for an exact match, with 4th param = 0) So, put instead in B2, copy down: =VLOOKUP(A2,Sheet1!A:B,2,0)- SUMPRODUCT(--(LEFT(Sheet2!$A$2:$A$100,2)=A2),Sheet2!$B$2:$B$100 ) Here's a sample construct: http://cjoint.com/?cdxdso01AH Deleting_Products_cj21_misc.xls -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "cj21" wrote in message ... Max Any chance you could post an example. Thanks for the help Chris -- cj21 ------------------------------------------------------------------------ cj21's Profile: http://www.excelforum.com/member.php...o&userid=25673 View this thread: http://www.excelforum.com/showthread...hreadid=508126 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combine information about products from 2 spreadsheets | Excel Worksheet Functions | |||
How do I tie prices to a drop down list of products in excel? | Excel Discussion (Misc queries) | |||
deleting values in a worksheet without deleting the formulas | Excel Worksheet Functions | |||
how prevent formula in cell from deleting when deleting value???? | New Users to Excel | |||
Combine query to count products with similar names | Excel Worksheet Functions |