Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi

    I have a list of alphabetically last names, first names and they are sorted.
    I' like to apply the Bold or not Bold conditional format as follow :

    All last names startin with letter A = BOLD
    Going down, names start with B = not bold
    Then last name starting with letter C may not be in the list :
    If last names start with C are present then =BOLD
    If not, then D=BOLD.
    And so on till Z
    I've been trying more than a dozen formula in the CF... but nothing works.

    TIA
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post
    Francis

    There is a way of doing this using only one Conditional Formatting rule.

    Assume your list of names is in column A, highlight the column and then in

    • Format
    • Conditional Formatting


    Change the drop down to Formula Is

    and paste this formula into the box:

    =MOD(CODE(LEFT(A1,1)),2)

    Change the format to bold.

    I have attached a workbook to show
    Jerry

  3. #3
    3 Star Lounger
    Join Date
    Feb 2003
    Location
    Runcorn, Cheshire, United Kingdom
    Posts
    372
    Thanks
    0
    Thanked 2 Times in 2 Posts
    [quote name='Jezza' post='762502' date='28-Feb-09 : 21:51']=MOD(CODE(LEFT(A1,1)),2)

    Change the format to bold.[/quote]

    That works only so long as you have names with all initial letters, and the request was (in effect) IF B is normal and there are 'C's, make Cs bold and Ds normal, but if there are no 'C's, make Ds bold and Es normal etc.


    I would do it by having a hidden column B, with code like this from cell B2 down.
    =IF(LEFT(A2)=LEFT(A1),MOD(B1,2),MOD(B1+1,2))
    and use that column to control the conditional formatting

    What it does is to alternate the hidden value between 0 and 1 every time the initial letter changes.

    Amended file attached.

    I'm not sure I actually like it much, because it's I'm not sure how well it'll work if you're adding and removing entries on the list
    Attached Files Attached Files

  4. #4
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='Gfamily' post='762519' date='01-Mar-2009 06:48']That works only so long as you have names with all initial letters, and the request was (in effect) IF B is normal and there are 'C's, make Cs bold and Ds normal, but if there are no 'C's, make Ds bold and Es normal etc.


    I would do it by having a hidden column B, with code like this from cell B2 down.
    =IF(LEFT(A2)=LEFT(A1),MOD(B1,2),MOD(B1+1,2))
    and use that column to control the conditional formatting

    What it does is to alternate the hidden value between 0 and 1 every time the initial letter changes.

    Amended file attached.

    I'm not sure I actually like it much, because it's I'm not sure how well it'll work if you're adding and removing entries on the list[/quote]


    Hi

    Thanks for your effort. I don't know much about the MOD function...guess I need to read and play around
    with this function more to understand it.
    Agreed with you on the last point, I 'll highlight this to the users to manage expectation.
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  5. #5
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='Jezza' post='762502' date='01-Mar-2009 05:51']Francis

    There is a way of doing this using only one Conditional Formatting rule.

    Assume your list of names is in column A, highlight the column and then in

    • Format
    • Conditional Formatting


    Change the drop down to Formula Is

    and paste this formula into the box:

    =MOD(CODE(LEFT(A1,1)),2)

    Change the format to bold.

    I have attached a workbook to show[/quote]

    Hi Jerry

    Thank you very much on this, although not exactly producing the result but your solution
    providing or facilitates to the correct solution by Gfamily

    Like I have mentioned, I am unfamiliar with the MOD function and need to play around with this more
    to understand it better.
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  6. #6
    3 Star Lounger
    Join Date
    Feb 2003
    Location
    Runcorn, Cheshire, United Kingdom
    Posts
    372
    Thanks
    0
    Thanked 2 Times in 2 Posts
    [quote name='franciz' post='762565' date='01-Mar-2009 06:34']Thanks for your effort. I don't know much about the MOD function...guess I need to read and play around[/quote]

    MOD is just a mathematical function that returns the remainder when one number is divided by another. I'm just using as a way of getting two alternating values using addition only.

    If the previous hidden value is zero, adding 1 gives 1, and the remainder when divided by 2 is 1.
    If the previous hidden value is 1, adding 1 gives 2, so the remainder when divided by 2 is 0.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •