Conditional Formatting Duplicates based on Date Comparisons

[Duke.Togo]

I apologize if this has been answered elsewhere, but searching didn't turn it up. My spreadsheet is setup in this manner (only showing columns needed for evaluation):

Date | Name

What I need to do is apply a format to a row only if the value in the Name column is duplicated in the table, and the difference between the Date in these duplicates is less than 30.

Thank you!

 

[pgc01]

Hi Duke.Togo
Welcome to the board

Assuming the dates in A2:A100 and the names in B2:B100, use for the conditional formatting in A2 the formula:

=SUMPRODUCT(--($B$2:$B$100=$B2),--(ABS($A$2:$A$100-$A2)<30),--($B$2:$B$100<>""))>1

Copy the format to A2:A100

 

[Duke.Togo]

That worked fantastically, thank you! In reading through I can understand the logic of the functions used, but I am not familiar with the "--" and what its function is. I will have to do some reading on SUMPRODUCT. Thanks again!

 

[pgc01]

Hi

the -- is the double unary minus, it simply coerces logical values into numbers, FALSE into 0 and TRUE into 1. This way you get an array of numbers, that can be processed by sumproduct.

Check here:

http://www.mrexcel.com/forum/showthread.php?t=385842