deleting products

[cj21]

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

 

[Max]

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)

---

 

[cj21]

Max

Any chance you could post an example.

Thanks for the help

Chri

 

[Max]

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