conditional formatting, duplicates with 3 criterias

[hyperion007]

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!

[venkat1926]

difficult to find out which is in col A,B,C,D and E. either use unique delimiter so that text to column facility can be used or use the facility in this newsgroup for attaching the workbook to the postings. The workbook should be small so attach only a very small extract of your sheet.

[hyperion007]

Ok, I'm no expert but I will attach a file for you to have a look at.

[arthurbr]

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 ?

[hyperion007]

Well nothing really. If I can just highlight the duplicates based on these three criterias then I can use the filter function to sort based on fill color.

[arthurbr]

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

[hyperion007]

Sure,

If there are two or more identical values in the E column, and those rows have equal values in column A and column C then they should be highlighted.

In the worksheet only row 8 and 9 should be highlighted.

Rows 4 and 5 have identical values in column E and column A but not in column C so it should not be highlighted.

[hyperion007]

Thanks but I would really like it if you could use my example and implement your rules on that one because I don't fully understand your example.

Bear in mind that in my example, I only want row 8 and 9 highlighted after applying the formula(s)

Thanks again.

[Marcol]

Try this

Conditional formatting
Formula is

Please Login or Register  to view this content.

Applies to

Please Login or Register  to view this content.

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

[hyperion007]

Thanks, I'll try this.

I can't remove the duplicates as these excel worksheets are only data exported from another program. I need to identify the duplicates quickly and then go to the source application and remove the duplicates there.

The time is actually a factor. If there are is a duplicate that has happened within about 5minutes of the first entry, it is considered a duplicate.

[hyperion007]

Also I don't seem to get it to work. I get an error message when trying your formula in conditional formatting / New Rule

[arthurbr]

@Marcol

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

[arthurbr]

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

[Marcol]

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.

[hyperion007]

That worked a treat!!!!!

Thanks! Been trying stuff for the last week and you solved it just like that! Great!

[Marcol]

Happy to help.

Try this workbook.

The difference is that it applies the 5min rule to Column B.

This may or may not be of further use.