Comparing two columns of time using Conditional Formatting In Excel

**kar82** · 11-01-2011, 09:03 PM

Hi all,
I am trying (and failing!!) to apply what should be an easy conditional format to two lists of times, so that if any cell from one column is the same as any cell in the other it should highlight both cells. I have attached a workbook example.
Thanks in advance.
K

**teylyn** · 11-01-2011, 09:13 PM

This conditional formula will do it. Use it with the Formula Is option

Select A3 to the end and enter this formula
=MATCH(A3,$B$3:$B$146,0)

Select B3 to the end and enter this formula
=MATCH(B3,$A$3:$A$146,0)

But in your data sample, there are no matches.

**kar82** · 11-01-2011, 09:34 PM

Thanks for your quick reply, this formatting has worked a treat after I put in some time frames that were the same. If I could ask one more question? Could I highlight in another colour, times from each column that occur within 5 seconds of each other, if Column A occurs first, in one colour and if Column B occurs first in another? Hopefully that makes sense.
Again thank you so much for the quick advice. Greatly appreciated!!

**teylyn** · 11-01-2011, 11:24 PM

Sorry, confused. Please post a sample with the highlights added manually.

**kar82** · 11-02-2011, 12:15 AM

Hiya, I am sorry for the confusion, even when I wrote it I was confused. I ahve attached the workbook with some more clarification on what I would want to occur. Thanks again for your time.
K

**er.diljeetsingh** · 11-02-2011, 12:29 AM

VBA CODE 1 -:

Option Explicit

Const WelcomePage = "Macros"

**teylyn** · 11-02-2011, 06:59 AM

Highlighting the first occurrance of a near match with a different color is difficult. I don't know if it can be done at all.

The formula to highlight near matches in column A is (starting in A3)
=SUMPRODUCT(--($B$3:$B$146<=A3+"00:00:05"),--($B$3:$B$146>=A3-"0:00:05"))

For column B, starting in B3
=SUMPRODUCT(--($A$3:$A$146<=B3+"00:00:05"),--($A$3:$A$146>=B3-"0:00:05"))

This evaluation must be performed before the exact match evaluation, otherwise the exact match highlighting will be overwritten.

By the way, some of the values include milliseconds, so even if they appear to be the same, they are really not. Format with custom format h:mm:ss.00 to see the difference.

cheers,

**Fotis1991** · 11-02-2011, 07:57 AM

Hi teylyn.

Amazing job.

I and many others, we learn many things in this forum from people like you.

Congratulations.

**kar82** · 11-02-2011, 07:08 PM

Hi teylyn,
Thanks for the pointer about the milliseconds, I have now formatted correctly and understand what you were saying. I have now added the two conditional formatting formulas prior to the exact evaluation. I have a significant amount of data and will check as I go along, but this has been such an amazing help. Thanks so much for your quick replies, it is greatly appreciated. I will mark this as solved!!!
K