Thread: conditional formatting, duplicates with 3 criterias

Hi all.

I have been searching the net for an answer for the last week and not been able to get this solved so here goes.

I need a way to display duplicates when three criterias are met. like filling these rows with red color.

I give you an example:
A....................B...............................C.......................D...................... ..E
71.............2010-10-03 16:43:22..2010-10-03................VS............402620******0960
37.............2010-10-02 16:45:19..2010-10-02................VS............402620******0965
129...........2010-10-04 12:49:48..2010-10-04 ................VS............402620******1014
196...........2010-10-06 18:22:51..2010-10-06 ................VS............402620******1104
196...........2010-10-15 15:21:42..2010-10-15 ................VS............402620******1104
59............2010-10-05 11:45:32..2010-10-05 ................VS............402620******1317
62............2010-10-04 19:53:28..2010-10-04 ................VS............402620******1362
56............2010-10-02 19:12:56..2010-10-02 ................VS............402620******1604
56............2010-10-02 19:13:42..2010-10-02 ................VS............402620******1604
67............2010-10-06 11:23:34..2010-10-06 ................VS............402620******1605
44............2010-10-04 16:46:39..2010-10-04 ................VS............402620******1728
42............2010-10-05 14:53:49..2010-10-05 ................VS............402620******2215



In the above example I want to highlight the two rows that start with '56' but not the rows that start with '196'

something like: highlight IF(($E$1:$E$100000,E1)>1,AND(IF($C$1:$C$100000,C1)>1)AND(IF($A$1:$A$100000,A1)>1))

but I have no luck getting a grip on the formula to be used to do this.

Help would be much appreciated!

In the above example I want to highlight the two rows that start with '56' but not the rows that start with '196'

And what should happen with the other values ?

It must be me, but could you explain which those three criteria are ?

Try this

Conditional formatting
Formula is

=AND($A1=$A2,DATE(YEAR($B1),MONTH($B1),DAY($B1))=DATE(YEAR($B2),MONTH($B2),DAY($B2)),$C1=$C2,$E1=$E2)

Applies to

=$A$1:$E$12

Adjust to suit your range

Format...... Your choice.

This will highlight duplicates, assuming your data is sorted and grouped as in your example.
The formula also takes Column B, ignoring the time, into consideration, just in case.

What are you eventually trying to do? Remove duplicates?

If so why not just use Remove Duplicates? (Select your table and uncheck Column B)

I am assuming that the time portion of column B is not relevant in this scenario.

Hope this helps

@Marcol

The OP didn't seem to need a test on the dates in col B as far as I could understand

And yes, you are right, I added a wrong example Sorry about that

Try this workbook

It only highlights the first row if a duplicate follows.
i.e.
If there are two consecutive rows the same in the group then the first is highlighted.
If there are three rows the same in the group then the first two are highlighted.....etc.

I have not considered the 5minute rule at present

Hope this helps.