Highlight Duplicates Within on Proximity/Date

[thefrostman]

Attached is my example sheet. My full worksheet has 30+ columns and 400+ rows. My goal is to highlight any cells that have a duplicate within 3 rows above or below them (1 week) in any column. Also don't highlight a cell if it has a duplicate in the same column.

So far I tried conditional formatting and it got me close. But I would have to make hundreds of rules and I can't seem to have them overlap.
I tried some formulas to show me the number of matches there are per row but it's not obvious enough and I'd like the cells highlighted.

Is there a formula I can use in conditional formatting so I only have to make 1 rule for the whole sheet? Would a macro be the way to go here?
Any help would be much appreciated.

[MarvinP]

Hi frostman and welcome to the forum,

I have a possible answer but don't like it. See the attached where I've used a countif formula across a range of cells. You may need the latest version of excel to do this formula in I2.

Please Login or Register  to view this content.

Then to get a date range I've done it again to the right in column N but one row down, repeat 6 times. Then copy from I2 to AQ8 below the above block. OUCH!!

Everywhere there is a 2 in the counter fields is where your answer lies.

Question - Are you tracking in the same week? Could you put a new column of WeekNumber and track dups in the same week? Is this what you really want?

Dups within 6 days.xlsx

[thefrostman]

That formula will highlight all duplicates within that range. I'm looking to narrow down the scope more.

I don't want to only look at one week at a time, like every Sunday to Saturday. I want to look any cell with a duplicate within 3 rows above or below. The 7 total rows will be a weeks range but in reference to the target cell.

My worksheet will have a lot of rows and cells so showing results to the side of it will be too much to look at. I think highlighting is the best way to easily find these.

[thefrostman]

I think there should be a way to do this with conditional formatting. I just need to find the right formula to highlight duplicates within the 3 rows above and below it and then apply that to the whole set of data. Thoughts?

[MarvinP]

Hey,

What happens if you have a duplicate 3 rows above and it shows using CF. Then the same happens 4 rows below. That city will be CF twice, once from above and once from below. You will have no idea which are duplicates of which. Even if you change colors of the CF it won't help as the last format will win.

I think you'd have better luck by arranging your data in 3 columns: Date, Client, City. Then you might come up with a formula using date range and CountIF() that would give an answer you could live with. I don't think a CF answer lives with the data as you've arranged it.

Maybe one of the smart Guru's can give a CF answer that I can't imagine...

[JeteMc]

This proposal employs five helper columns which may be moved and/or hidden for aesthetic purposes.
1. Column I produces a list of locations using*: =IFERROR(INDIRECT(TEXT(SMALL(IF((C$2:G$31<>""),ROW(A$2:A$31)*100+COLUMN(C$1:G$1),10^10),ROWS(A$1:A1)),"R0C00"),)&"","")
2. Column J produces a list of corresponding clients using*: =IF(I2="","",INDEX(A$1:G$1,IFERROR(VALUE(RIGHT(TEXT(SMALL(IF((C$2:G$31<>""),ROW(A$2:A$31)*100+COLUMN(C$1:G$1),10^10),ROWS(A$1:A1)),"R0C00"),2)),"")))
3. Column K produces a list of corresponding dates using: =IF(I2="","",INDEX(A$2:A$31,AGGREGATE(15,6,(ROW(A$2:A$31)-COLUMN(A$1))/(C$2:G$31=I2),COUNTIFS(I$2:I2,I2))))
4. Column L produces a list of number of duplicates within +/- three days using: =IF(I2="","",COUNTIFS(I$2:I$31,I2,J$2:J$31,"<>"&J2,K$2:K$31,">="&K2-3,K$2:K$31,"<="&K2+3))
5. Column M produces a list of cell addresses that contain duplicates using: =IF(I2="","",IF(L2>0,ADDRESS(MATCH(K2,A$1:A$31,0),MATCH(J2,A$1:G$1,0)),""))
The conditional formatting formula is: =COUNTIFS($M$2:$M$31,ADDRESS(ROW(),COLUMN()))
*Denotes an array entered formula which is confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.
Let us know if you have any questions.

[MarvinP]

Hi frostman,

I see that a "Smart Guru" JeteMc figured your problem out above. It looks like he needed 5 helper columns and lots of CSE Array formulas. Does this solve your problem?

Marv