ExcelBanter - 2-digit

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - 2-digit - 6-digit (https://www.excelbanter.com/excel-discussion-misc-queries/66069-2-digit-6-digit.html)

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

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

2-digit - 6-digit

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

Chris

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

2-digit - 6-digit

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" wrote in message
...

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

2-digit - 6-digit

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

2-digit - 6-digit

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"cj21" wrote in message
...

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

2-digit - 6-digit

Hopefully this attachment shows an example of my dataset and what i want
to do

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

2-digit - 6-digit

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 |
+-------------------------------------------------------------------+

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