Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filtering by Term Digits
I'm a TL at an attorneys office and our vendor file numbers are in the
following format: VEN-0111223 I deal with large spreadsheets that I was manually splitting up and delegating to associates by filtering and giving myself major headaches (not to mention taking up most of my morning just giving them work. Thank you Tech department from hell). I'm in the process of trying to macro it out to where I come in and refresh the data with all of the new files for the day and have a macro split up their work into their specific portfolio assignments. The problem I'm running into is that I have associates assigned by client then by ODD and EVEN VEN numbers then further split by client. The client split is easy but identifying odd and even text/numeric fields is proving to be difficult. All of the formulas I found on the forum are for number fields only. I need to be able to take a spreadsheet full of VEN-0111223 and split them out by odd's and even's. I've used the =ISODD(C11) function but it only works on the loan number fields and their work is driven by the VEN numbers. Can anybody here help me out with this lovely task? I would be a very happy camper. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filtering by Term Digits
Well, to determine odd/even, you really only need to look at the last digit
in the VEN number. So, try this: =ISODD(RIGHT(C11,1)) HTH, Elkar HTH, Elkar "christina" wrote: I'm a TL at an attorneys office and our vendor file numbers are in the following format: VEN-0111223 I deal with large spreadsheets that I was manually splitting up and delegating to associates by filtering and giving myself major headaches (not to mention taking up most of my morning just giving them work. Thank you Tech department from hell). I'm in the process of trying to macro it out to where I come in and refresh the data with all of the new files for the day and have a macro split up their work into their specific portfolio assignments. The problem I'm running into is that I have associates assigned by client then by ODD and EVEN VEN numbers then further split by client. The client split is easy but identifying odd and even text/numeric fields is proving to be difficult. All of the formulas I found on the forum are for number fields only. I need to be able to take a spreadsheet full of VEN-0111223 and split them out by odd's and even's. I've used the =ISODD(C11) function but it only works on the loan number fields and their work is driven by the VEN numbers. Can anybody here help me out with this lovely task? I would be a very happy camper. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filtering by Term Digits
If your file numbers are always of the form VEN-number, then the
following formula will return "odd" or "even", assuming the data is in A1: =IF(MOD(RIGHT(A1,LEN(A1)-4),2),"odd","even") Is this something you can work with? Hope this helps. Pete christina wrote: I'm a TL at an attorneys office and our vendor file numbers are in the following format: VEN-0111223 I deal with large spreadsheets that I was manually splitting up and delegating to associates by filtering and giving myself major headaches (not to mention taking up most of my morning just giving them work. Thank you Tech department from hell). I'm in the process of trying to macro it out to where I come in and refresh the data with all of the new files for the day and have a macro split up their work into their specific portfolio assignments. The problem I'm running into is that I have associates assigned by client then by ODD and EVEN VEN numbers then further split by client. The client split is easy but identifying odd and even text/numeric fields is proving to be difficult. All of the formulas I found on the forum are for number fields only. I need to be able to take a spreadsheet full of VEN-0111223 and split them out by odd's and even's. I've used the =ISODD(C11) function but it only works on the loan number fields and their work is driven by the VEN numbers. Can anybody here help me out with this lovely task? I would be a very happy camper. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filtering by Term Digits
That works great! i forgot to also take into consideration when we have
multiple files for the same account our file numbers add an A or a B to the end of the file number. for example VEN-00111223A VEN-00111223B I am receiving the dreaded #VALUE! on these fields. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filtering by Term Digits
"Elkar" wrote: Well, to determine odd/even, you really only need to look at the last digit in the VEN number. So, try this: =ISODD(RIGHT(C11,1)) HTH, Elkar HTH, Elkar This is a great solution. You might need to trim the source value because trailing spaces will cause an error. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filtering by Term Digits
"christina" wrote: That works great! i forgot to also take into consideration when we have multiple files for the same account our file numbers add an A or a B to the end of the file number. for example VEN-00111223A VEN-00111223B I am receiving the dreaded #VALUE! on these fields. Change the RIGHT() function to the MID() function. If the low-order digit of the number is always in position 12, you would use =ISODD(MID(C11,12,1)). If the low-order digit is not always in the same character position, reorganize the system so that it is. :) B+ HALinNY |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filtering by Term Digits
And just in case you see A as odd and B as even (yeah, not much change <bg).
=ISODD(code(RIGHT(C11,1))) =isodd() is part of the analysis toolpak addin. christina wrote: That works great! i forgot to also take into consideration when we have multiple files for the same account our file numbers add an A or a B to the end of the file number. for example VEN-00111223A VEN-00111223B I am receiving the dreaded #VALUE! on these fields. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count number of digits in a cell | Excel Worksheet Functions | |||
data filtering based on last two digits of large number | Excel Worksheet Functions | |||
Mistery digits on excel | Excel Discussion (Misc queries) | |||
Filtering by digits | Excel Discussion (Misc queries) | |||
Does excel recognise names rather than cells? | Excel Worksheet Functions |