#1   Report Post  
Posted to microsoft.public.excel.misc
cj21
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Max
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
cj21
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Max
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Combine information about products from 2 spreadsheets wstaylor81 Excel Worksheet Functions 1 December 15th 05 01:38 AM
How do I tie prices to a drop down list of products in excel? trspds Excel Discussion (Misc queries) 2 December 2nd 05 08:46 PM
deleting values in a worksheet without deleting the formulas patti Excel Worksheet Functions 1 October 28th 05 09:49 PM
how prevent formula in cell from deleting when deleting value???? sh-boom New Users to Excel 1 September 30th 05 06:12 PM
Combine query to count products with similar names pomalley Excel Worksheet Functions 8 April 22nd 05 02:15 AM


All times are GMT +1. The time now is 10:06 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"