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