Conditional Format tweak again
Posted by Barry on October 22, 2001 12:13 PM
First, thanks to lenze......that helped! But now I have another problem. Okay, the cells with the trailer numbers range from D9:D142, these trailer #'s are anywhere from 2 to 6 digits long and they all start with "M-". For condition 1 =ISBLANK(D9:D142) w/ no format, works great - COndition 2 =SUMPRODUCT((ISNUMBER(SEARCH(D9,$D$9:$D$142)))+0)>1 works fine except, it recognizes trailer M-26 the same as M-269876 which is found as being the same as M-269888........how can I get thge formula to compare the ENTIRE number all the way out to the 6th digit?
Posted by Juan Pablo on October 22, 2001 12:41 PM
Check out my response to your post, i suggested the a Countif function that compares the ENTIRE text.
Juan Pablo
Posted by Aladin Akyurek on October 22, 2001 12:55 PM
> First, thanks to lenze......that helped!
That's great, Barry, however I couldn't find the exchange between you and lenze.
>But now I have another problem. Okay, the cells with the trailer numbers range from D9:D142, these trailer #'s are anywhere from 2 to 6 digits long and they all start with "M-". For condition 1 =ISBLANK(D9:D142) w/ no format, works great - COndition 2 =SUMPRODUCT((ISNUMBER(SEARCH(D9,$D$9:$D$142)))+0)>1 works fine except, it recognizes trailer M-26 the same as M-269876 which is found as being the same as M-269888........how can I get thge formula to compare the ENTIRE number all the way out to the 6th digit?
I believe I gave you this formula.
Change the formula to
=SUMPRODUCT((ISNUMBER(SEARCH(LEFT(D9,8),$D$9:$D$142)))+0)>1
in order to compare up to the 6 digit. It's the first eight chars, so to speak.
It's a good thing that you range is not too big, otherwise the calculations with this formula would be too slow to bear.
Aladin
Posted by Barry on October 23, 2001 4:59 AM
Thanks Juan.....the first time I tried it, it wouldn't work??? But it seems to be working fine now..........thanks again!
Barry
Posted by Aladin Akyurek on October 23, 2001 5:46 AM
Barry --
If it's the COUNTIF formula that works fine, I have to conclude that your problem wasn't/is not a case of doing partial matches for which the costly SUMPRODUCT formula is intended to apply.
Aladin
======== Thanks Juan.....the first time I tried it, it wouldn't work??? But it seems to be working fine now..........thanks again!
