Conditional Formatting based on multiple criteria

[Shabbo]

Dear Sir,

I wanted to highlight duplication entries based on multiple conditions.

Example: if Column C that is entry date, Column E Vehicle number, Column G Type of expenses if same then Column B entry number should be highlighted as duplicate entry.

Example: if Column C that is entry date, Column E Vehicle number is same but columns G Type of expenses are not same then Column B entry number should not be highlighted as duplicate entry.

 

[NARAYANK991]

Hi ,

See if this is OK.

Narayan

 

[Shabbo]

Hi ,

See if this is OK.

Narayan

Dear Sir,
It worked, perfectly what I wanted thank you so much,
Can you please let me know how to apply for my current sheet.

 

[NARAYANK991]

Hi ,

The formula used in the .xlsx workbook is :

=COUNTIFS($C$2:$C$5, $C2, $E$2:$E$5, $E2, $G$2:$G$5, $G2) > 1

This CF formula is applied to the range $A$2:$I$5.

Since the COUNTIFS function ia available only in Excel 2007 and later versions , for your .xls file , you can use the following formula :

=SUMPRODUCT(($C$2:$C$5 = $C2) * ($E$2:$E$5 = $E2) * ($G$2:$G$5 = $G2)) > 1

Narayan

 

[Shabbo]

Hi ,

The formula used in the .xlsx workbook is :

=COUNTIFS($C$2:$C$5, $C2, $E$2:$E$5, $E2, $G$2:$G$5, $G2) > 1

This CF formula is applied to the range $A$2:$I$5.

Since the COUNTIFS function ia available only in Excel 2007 and later versions , for your .xls file , you can use the following formula :

=SUMPRODUCT(($C$2:$C$5 = $C2) * ($E$2:$E$5 = $E2) * ($G$2:$G$5 = $G2)) > 1

Narayan

It worked thanks.

 

[Charlie Day]

Hello,

I have 10 columns of data, each column has different set of values(one column has values ranging from .01 - 10 and another column has values ranging from 10,000 - 50,000), right now I'm selecting each column individually and applying conditional formatting>Color Scales. Is there a way for us to apply color scales to all the columns?. Format Painter for all columns does not work since each column has different set of values and are not related to each other (when I do that its treating entire column with .01-10 as red and entire columns with 10,000 -50,000 as green). I want to apply conditional formatting color scales on each column. I've been searching on the internet for answers but I can;t find the exact solution. I also can;t fine the color scale as a format if I'm selecting the formula. Please help.

Thanks