Conditional Formatting for unique items

Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: Conditional Formatting for unique items

  1. #1
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    13,422
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    3 Thread(s)

    Default Conditional Formatting for unique items

     
    Sorry, this is a long post but i could not find out another way to explain my problem...

    This is what i have:

    A B C D E F
    NamesPointsFrequency Formula hereCounting unique
    bob112
    sue10
    helen50
    bob10
    michael50
    michael80
    sue40
    john60
    bob41
    maria80


    Array-Formula in D3:D12 (part of the formula for counting unique)
    {=--(FREQUENCY(B3:B12,IF(A3:A12="bob",B3:B12,-ROW()))>0)}
    CF formula for D3:D12 is:
    =D3=1

    Array-Formula in F3 (counting unique)
    {=SUM(--(FREQUENCY(B3:B12,IF(A3:A12="bob",B3:B12,-ROW()))>0))-1}

    I was using D as an auxiliary column for the CF in A3:B12 using the formula:
    =$D3=1
    Everything worked fine as above.

    But when i try to eliminate the auxilary D-column simply substituing $D3 for the formula
    =--(FREQUENCY($B3:$B12,IF($A3:$A12="bob",$B3:$B12,-ROW()))>0)=1
    (as far as i can see, i'm only changing $D3 for the formula)
    i get this:

    NamesPointsFrequency Formula hereCounting unique
    bob112
    sue10
    helen50
    bob10
    michael50
    michael80
    sue40
    john60
    bob41
    maria80


    That is obviously wrong for the second "bob", since its not an unique item.

    What am i missing?

    Thanks in advance for any help.

    M.
    Last edited by Marcelo Branco; Oct 2nd, 2010 at 03:57 AM.

  2. #2
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    13,422
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Conditional Formatting for unique items

    ooops...

    my formula for counting unique had a flaw

    Fixing
    {=SUM(--(FREQUENCY(B3:B12,IF(A3:A12="bob",B3:B12,-ROW()))>0))-IF(AND(A12="bob",B12=MAX(B3:B12)),0,1)}

    I know this is an unusual way to counting unique, but i think it works

  3. #3
    MrExcel MVP DonkeyOte's Avatar
    Join Date
    Sep 2002
    Location
    Suffolk, UK
    Posts
    9,118
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Formatting for unique items

    Another alternative rule might be:

    Code:
    =IF($A3="bob",ISERROR(MATCH($A3&"@"&$B3,$A$2:$A2&"@"&$B$2:$B2,0)))
    Does my a$$ look big in this picture ?

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,006
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Formatting for unique items

    Formula to invoke in CF...

    =INDEX(FREQUENCY($B$3:$B$12,IF($A$3:$A$12="bob",$B$3:$B$12,-ROW())),ROWS($A$3:A3))
    Assuming too much and qualifying too much are two faces of the same problem.

  5. #5
    MrExcel MVP DonkeyOte's Avatar
    Join Date
    Sep 2002
    Location
    Suffolk, UK
    Posts
    9,118
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Formatting for unique items

    @Aladin - surely it would be better to conduct a pre-emptive test for "Bob" ? Thereby avoiding needless process of Frequency array.
    Does my a$$ look big in this picture ?

  6. #6
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    13,422
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Conditional Formatting for unique items

    Donkey,

    I found some problems with your formula. Only the first bob was correctly formatted.

    Maybe i have made some typos - i have to translate the formulas to portuguese (Excel 2007 pt-version).

    Sometimes its a hard job

    Aladin,

    Your formula worked perfectly

    Probably no typos

    Tks very much you two

    M.

  7. #7
    MrExcel MVP DonkeyOte's Avatar
    Join Date
    Sep 2002
    Location
    Suffolk, UK
    Posts
    9,118
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Formatting for unique items

    proof of concept:

    Sheet4

     AB
    2NamesPoints
    3bob1
    4sue1
    5helen5
    6bob1
    7michael5
    8michael8
    9sue4
    10john6
    11bob4
    12maria8

    Conditional formatting
    CellNr.: / ConditionFormat
    A31. / Formula is =IF($A3="bob",ISERROR(MATCH($A3&"@"&$B3,$A$2:$A2&"@"&$B$2:$B2,0)))Abc


    Excel tables to the web >> Excel Jeanie HTML 4

    it is assumed A3:B12 is active selection when rule applied (and A3 was highlighted first when range selected)
    Does my a$$ look big in this picture ?

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,006
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Formatting for unique items

    Quote Originally Posted by DonkeyOte View Post
    @Aladin - surely it would be better to conduct a pre-emptive test for "Bob" ? Thereby avoiding needless process of Frequency array.
    Sure. I was merely preoccupied with getting Branco's unique count formula to apply to CF...
    Assuming too much and qualifying too much are two faces of the same problem.

  9. #9
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    13,422
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Conditional Formatting for unique items

    Donkey and Aladin,

    Tks very much for your help.

    For sure Donkey formula is correct and i've made some mistake in typing it.

    I'll try tomorrow, since its 5:45 am in Rio and i didnt sleep till now trying different things in Excel

    testing, testing and ...testing...

    I think its the only way to learn something

    bye

    M.

  10. #10
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    13,422
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Conditional Formatting for unique items

      
    Aladin and Donkey,

    Quote Originally Posted by Aladin Akyurek View Post
    Sure. I was merely preoccupied with getting Branco's unique count formula to apply to CF...
    I'm back and still puzzled why my idea of using only one formula for counting and CF doesn't work...

    Why two formulas if counting-unique and CF-unique have essentially the same logic?


    M.
    ps: i was making a translation-mistake when entering Donkey-formula.
    ISERROR = ÉERROS in portuguese
    and i was using ÉERRO (without the last "s") that is = ISERR in english
    hard life

    Fixing it Donkey-formula = 100%

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com