software

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
020004I 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: www.excelforum.com/member.php...oamp;userid=25673
View this thread: www.excelforum.com/showthread...hreadid=502833
The way this thing posts eliminates spaces. hopefully you guys can still
understand.

Chris--
cj21
------------------------------------------------------------------------
cj21's Profile: www.excelforum.com/member.php...oamp;userid=25673
View this thread: www.excelforum.com/showthread...hreadid=502833Assuming 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)lt;SUM(IF(MAX(IF($B3:$B$20lt;gt;quot;quot;,ROW($B3 :$B$20)))=0,
MAX(ROW($B3:$B$20)),MAX(IF($B3:$B$20lt;gt;quot;quot;,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)

quot;cj21quot; gt; wrote in message
...
gt;
gt; I have two lists of product codes (2-digit and 6-digit), each of which
gt; has an import value (in otherwords 4 columns) as follows:
gt;
gt; 1 2 3
gt; 4
gt; Prod code (2 digit) importval ($) Prod code (6-digit) import
gt; value ($)
gt; 01 50 010001 25
gt; 010002 10
gt; 010003 15
gt;
gt; 02 75 020001 12
gt; 020002 18
gt; 020003 20
gt; 020004 25
gt;
gt; As you can see the 6-digit product codes are a dissaggregated from the
gt; 2-digit codes. That is if you sum the value of the 6-digits imports it
gt; will equal the value of the 2-digits imports. So for product 01 = $50 =
gt; 010001 010002 010003=50. The same occurs for product 02 and so in for
gt; my real data set.
gt;
gt; Now my problem is this: I have a list of products i wish to exclude
gt; (called exceptions)
gt;
gt; e.g 010001
gt; 020002
gt; 020004
gt;
gt;
gt; I want a formula that plucks out these products from column 3 and
gt; subtracts there import value (colomn 4) from cloumn 2 (the 2-digit
gt; import value). Thus giving me a new column of 2-digit product codes
gt; excluding the exceptions. In my example this would look like:
gt;
gt; 5 6
gt; Prod Code 2-dig New Import Val
gt; 01 25
gt; 02 32
gt;
gt; Obviously i have alot of data so doing this manuely would take a long
gt; time. Are there any formulas out there?
gt;
gt; Chris
gt;
gt;
gt; --
gt; cj21
gt; ------------------------------------------------------------------------
gt; cj21's Profile:
www.excelforum.com/member.php...oamp;userid=25673
gt; View this thread: www.excelforum.com/showthread...hreadid=502833
gt;

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: www.excelforum.com/member.php...oamp;userid=25673
View this thread: www.excelforum.com/showthread...hreadid=502833I've posted it at cjoint.com/?btpAaHnomf

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;cj21quot; gt; wrote in message
...
gt;
gt; sorry i cant get this to work. any chance you could post an attachment
gt; to show how you have got it to work.
gt;
gt; Thankyou for your help
gt;
gt; Chris
gt;
gt;
gt; --
gt; cj21
gt; ------------------------------------------------------------------------
gt; cj21's Profile:
www.excelforum.com/member.php...oamp;userid=25673
gt; View this thread: www.excelforum.com/showthread...hreadid=502833
gt;

Hopefully this attachment shows an example of my dataset and what i want
to do--
cj21
------------------------------------------------------------------------
cj21's Profile: www.excelforum.com/member.php...oamp;userid=25673
View this thread: www.excelforum.com/showthread...hreadid=502833
Hopefully this attachment shows an example of my dataset and what i want
to do--
cj21
------------------------------------------------------------------------
cj21's Profile: www.excelforum.com/member.php...oamp;userid=25673
View this thread: www.excelforum.com/showthread...hreadid=502833
Hopefully this attachment shows an example of my dataset and what i want
to do--
cj21
------------------------------------------------------------------------
cj21's Profile: www.excelforum.com/member.php...oamp;userid=25673
View this thread: 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? -------------------------------------------------------------------
|Filename: Prod code.doc |
|Download: www.excelforum.com/attachment.php?postid=4249 |
-------------------------------------------------------------------

--
cj21
------------------------------------------------------------------------
cj21's Profile: www.excelforum.com/member.php...oamp;userid=25673
View this thread: www.excelforum.com/showthread...hreadid=502833