2-digit - 6-digit

[cj21]

I have two lists of product codes (2-digit and 6-digit), each of which
has an import value (in otherwords 4 columns) as follows:

1 2 3 4
Prod code (2 digit) importval ($) Prod code (6-digit) import value ($)
01 50 010001 25
010002 10
010003 15

02 75 020001 12
020002 18
020003 20
020004 25

As you can see the 6-digit product codes are a dissaggregated from the
2-digit codes. That is if you sum the value of the 6-digits imports it
will equal the value of the 2-digits imports. So for product 01 = $50 =
010001 +010002+010003=50. The same occurs for product 02 and so in for
my real data set.

Now my problem is this: I have a list of products i wish to exclude
(called exceptions)

e.g 010001
020002
020004


I want a formula that plucks out these products from column 3 and
subtracts there import value (colomn 4) from cloumn 2 (the 2-digit
import value). Thus giving me a new column of 2-digit product codes
excluding the exceptions. In my example this would look like:

5 6
Prod Code 2-dig New Import Val
01 25
02 32

Obviously i have alot of data so doing this manuely would take a long
time. Are there any formulas out there?

See the attachment

Chris


+-------------------------------------------------------------------+
|Filename: 2DIG.doc |
|Download: http://www.excelforum.com/attachment.php?postid=4244 |
+-------------------------------------------------------------------+

 

[Bob Phillips]

I posted you a working solution in response to the thread in excel.misc.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[cj21]

Thankyou for your help. It has save me alot of time. However is it
possible to create a list like on the attachment i have added to this
document?

Also another problem. When i cut and paste data, it usually takes the
formula, which means my computer performs alot of calculations and
usually ends up crashing. Is it possible to essentially take a snap
shot when cutting or copying so the formulas are not transferred but
the values stay the same?


+-------------------------------------------------------------------+
|Filename: Prod code.doc |
|Download: http://www.excelforum.com/attachment.php?postid=4248 |
+-------------------------------------------------------------------+