2-digit - 6-digit

  • Thread starter Thread starter cj21
  • Start date Start date
C

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?

Chris
 
Assuming that you have the list of exclusions in M1:M10, use this formula in
the totals field (column B)

=SUM(IF((NOT(ISNUMBER(MATCH(C2:$C$20,$M$1:$M$10,0))))*
(ROW(A2:$A$20)<SUM(IF(MAX(IF($B3:$B$20<>"",ROW($B3:$B$20)))=0,
MAX(ROW($B3:$B$20)),MAX(IF($B3:$B$20<>"",ROW($B3:$B$20)))))),D2:$D$20))

That is the first one, which currently shows 50, so just copy that to the
other one.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
sorry i cant get this to work. any chance you could post an attachment
to show how you have got it to work.

Thankyou for your help

Chris
 
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=4249 |
+-------------------------------------------------------------------+
 
Back
Top