Highlighting Duplicates

[chssundeep]

Hi Friends,

I need your help in solving this.......

I need excel to highlight the entire row if the condition fails...... tried with conditional formatting could not make it.....

Please help me..... Attached the sample file

 

[Chihiro]

Couple of questions.
1. Is Commission pulled from some source data or is it typed in?
I ask, because you can write a formula for this if it's entered manually and avoid error. Thus no need for check with CF.

2. Your condition will not capture following. How do you want it treated?
- You have "greater than" for both 10,000 & 15,000 condition. Should it be "greater than or equal to"?

- What about amount from 9,995 to 9,999? It's not specified how these should be treated.

 

[chssundeep]

Thanks Chihiro for your time

1. The commission is typed manually by different groups and comes to me.......I need to check it....... So I need excel to highlight it if there is a difference..

2. 0 - 9999 the commission is "0"
10000 - 14999 the commission is 500
above 15000 the commission is 1000

 

[Deepak]

Is this....

 

[chssundeep]

exactly the same @ Deepak........

Thank you so much........Please explain me the formula

Could you also look into the second sheet please

 

[chssundeep]

Please explain this part

=AND(

IF($C1<10000,0,IF($C1<15000,500,1000))

<>$D1,$D1<>"",$C1<>"Amount")

Using And means combing two logic's

Next you have used If function to get 0 or 500 or 1000

Then you asked excel to check whether it is equal to D1

The below part I couldnot understand

$D1<>"",$C1<>"Amount")

Please explain me........Thank you

 

[Chihiro]

Since CF is applied to range $C:$D (i.e. entire C & D columns).

$D1<>"" checks that cell that CF applies to is not blank.
$C1<>"Amount" excludes header row with "Amount" (i.e. Row #4).

 

[chssundeep]

Thanks for the explanation.......

Chihiro please look into the second sheet (Sheet2) in the sample and provide me a solution

 

[Chihiro]

See attached.

 

[chssundeep]

Thank you very much Chihiro