Highlight the Duplicates

[chssundeep]

Hi Excel Lovers...

I need a solution from my problem...

In Column A i have months and in Column B we have numbers..

Now I am looking for a formula to highlight the complete cell if a number is repeated in the same month twice

For example a 610 number can be once in a month........ if 610 number comes twice in the month of July it should be highlighted else its not a duplicate...

Attached the excel sheet with my sample problem

 

[Luke M]

Change your CF to be based on a formula, and use a formula of:
=COUNTIFS($B$6:$B$22,$B6,$C$6:$C$22,$C6)>1

 

[Xiq]

Hi chssundeep,

There are a couple of options:

1. A helper column where you concatenate the two values (e.g.: =B6&C6 ) + run the 'Conditional Formatting'-rule on the helper column.
2. Make a formula based CF-rule (see below).

=SUMPRODUCT(--($B$6:$B$22&$C$6:$C$22=B6&C6))>1

Please see attached for both options.

 

[chssundeep]

Dear Luke,

Thank you very much for your valuable time........ That ]s working perfectly..

When I tried to apply I am ending with one more issue.....Please guide me where I am going wrong....

I have selected C and D columns and have applied the below formula in CF

=COUNTIFS(C:C,C1,D:D,D1)>1

and selected applies to =$C:$D

Error I am facing.......

The complete Column D turned Red and in Column C the duplicates got highlighted...

I applied the same formula in the next column i.e. in Column E...There I am getting True and False correctly........But the colouring was missed...Please guide me on this...

 

[chssundeep]

Dear Xig,

Thanks for the solution......... Loved the idea of Helper column...

The second solution in Sumproduct why you have used --- ??

Once again thank you very much for your time

 

[Xiq]

Dear Xig,

Thanks for the solution......... Loved the idea of Helper column...

The second solution in Sumproduct why you have used --- ??

Once again thank you very much for your time

In my formula I compare one value with another (e.g.: A1 = B1), this will result in either TRUE or FALSE. The SUMPRODUCT function does not know how to deal with that (only numbers). Luckily you can "trick" Excel into forcing TRUE/FALSE results back into numbers by using math operators.

So, for example TRUE+TRUE is equal to 2. Or (like in my formula) --TRUE is equal to 1 ( a double negative = positive )

 

[Luke M]

In my solution, when you applied it to the whole column, the relative references of C:C and D:D, which are in relation to C1. However, in col D, that relation was pointing to D:D and E:E.

First, I'd suggest not applying CF to a whole column, as that's formatting over 2 million cells. But if you do want to do that, formula would be:
=AND(NOT(ISBLANK(C1)),COUNTIFS($C:$C,$C1,$D:$D,$D1)>1)

NOte that I added in a check to make sure the cell is not blank (as we don't want to highlight all the blank rows.