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)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"cj21" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[cj21]

Max

Any chance you could post an example.

Thanks for the help

Chris

[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

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"cj21" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>