Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a Check Digit | Charts and Charting in Excel | |||
How to replace the last digit in a cell with a letter | Excel Discussion (Misc queries) | |||
Excell Check Digit Formula | Excel Worksheet Functions | |||
Return a digit in a string of numbers | Excel Discussion (Misc queries) | |||
Tell users how to sort 5 digit and 9 digit zipcodes correctly aft. | New Users to Excel |