Matching duplicates based on multiple criteria, conditional formatting?

Hi all,

I have the below sample data set:
ID Address Date
123 45 Mall way 12/19/2013
123 45 Mall way 12/19/2013
112 22 Viper st 12/19/2013
112 22 Viper st 12/19/2013
112 22 Viper st 12/20/2013
112 22 Viper st 12/18/2013
111 12 Jog ct. 12/11/2013
113 15 Key blvd. 12/10/2013

I want to conditionally format duplicate rows of data based on ID, Address, and Date - but the date can be plus or minus a day or two.

Is it possible?

Thanks in advance.

Highlight the data in columns A, B and C, starting from A3, then click on Conditional Formatting | New Rule | Use a Formula ...

Enter this formula in the box:

=AND($A2=$A3,$B2=$B3,ABS($C2-$C3)<3)

Click the Format button, select the Fill tab, then choose your colour and OK your way out. Excel will automatically adjust those cell references to suit the selected data. This will highlight the duplicated rows, but not the first of the duplicates.

Hope this helps.

Pete

Thank you Pete_UK.

It sorta worked. It doesn't highlight all of the duplicates, just some. In a few rows I have 4 duplicates, and only two were higlighted. Maybe the formula is a little off because the data can have up to 20 duplicates?

I appreciate your feedback!

Could it be that your dates are really date-times with only the dates showing?

Or could you have leading and/or trailing spaces or multiple spaces in the address?

Attach a sample workbook showing more variations than in your first post.

Pete

Not sure if I attached one correctly. Excel forum sample.xlsx

You have some spaces in your addresses. Highlight the data from A2 to E28, click on Conditional Formatting | New Rule | Use a formula, then enter this formula in the box:

=AND($A1=$A2,TRIM($C1)=TRIM($C2),ABS($E1-$E2)<3)

then click on the Format button as before, select Fill tab and choose your colour and OK your way out.

Hope this helps.

Pete