# Conditional Formatting for unique items

This is a discussion on Conditional Formatting for unique items within the Excel Questions forums, part of the Question Forums category; Sorry, this is a long post but i could not find out another way to explain my problem... This is ...

1. ## 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
 Names Points Frequency Formula here Counting unique bob 1 1 2 sue 1 0 helen 5 0 bob 1 0 michael 5 0 michael 8 0 sue 4 0 john 6 0 bob 4 1 maria 8 0

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:

 Names Points Frequency Formula here Counting unique bob 1 1 2 sue 1 0 helen 5 0 bob 1 0 michael 5 0 michael 8 0 sue 4 0 john 6 0 bob 4 1 maria 8 0

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.

2. ## 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. ## 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)))`

4. ## 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))

5. ## 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.

6. ## 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

Your formula worked perfectly

Probably no typos

Tks very much you two

M.

7. ## Re: Conditional Formatting for unique items

proof of concept:

Sheet4

 A B 2 Names Points 3 bob 1 4 sue 1 5 helen 5 6 bob 1 7 michael 5 8 michael 8 9 sue 4 10 john 6 11 bob 4 12 maria 8

Conditional formatting
 Cell Nr.: / Condition Format A3 1. / 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)

8. ## Re: Conditional Formatting for unique items

Originally Posted by DonkeyOte
@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...

9. ## Re: Conditional Formatting for unique items

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. ## Re: Conditional Formatting for unique items

Originally Posted by Aladin Akyurek
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%

Page 1 of 2 12 Last

#### Posting Permissions

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