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.

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.

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.

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.

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.

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

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