2-digit - 6-digit

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

The way this thing posts eliminates spaces. hopefully you guys can still understand.

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)

"cj21" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
>
> --
> cj21
> ------------------------------------------------------------------------
> cj21's Profile:
http://www.excelforum.com/member.php...o&userid=25673
> View this thread: http://www.excelforum.com/showthread...hreadid=502833
>

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

I've posted it at http://cjoint.com/?btpAaHnomf

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"cj21" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
>
> --
> cj21
> ------------------------------------------------------------------------
> cj21's Profile:
http://www.excelforum.com/member.php...o&userid=25673
> View this thread: http://www.excelforum.com/showthread...hreadid=502833
>

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?