CODEWITHSUNDEEP
vbaexcel
Kiwizoom
Kiwizoom

Reputation: 443

Excel format rule highlight duplicates based on each date column entry

This excel doc is kind of like a guestbook, where there are numerous people's names and the date they visited on (written like 11/17/2014).

The doc is sorted by date, so there is like 100 or 200 names for 11/17/2014, then 200 or so for 11/18/2014... it goes on for a bunch of consecutive dates.

I want to write a formatting rule where it highlights if there are name duplicates during the duration of each day in the date column. This is to get rid of duplicates, and have an accurate representation of people visiting per day.

Things I tried:
regular dupe checker in conditional formatting - it is easy to run a dupe check all within one column. But this is a dupe check based on two columns. There will be many dupes for visitors returning daily.

The built-in formatting rule custom formula writer - it is easy to say "highlight all occurrences where column A cell and column B cell are equal" but not if the two cell patterns occurred more than once.

The Macro writer - I'm pretty rusty on visualbasic so I might be faster at generic dupe checking 100k or so entries by manually highlighting each day range.

TL;DR - Highlight all where 'name' and 'date' pattern occur more than once.

Any suggestions?

Upvotes: 1

Views: 1607

Answers (1)

Raugmor
Raugmor

Reputation: 566

conditional formatting formula would be:

=IF(COUNTIFS($A:$A,$A1,$B:$B,$B1)>1, TRUE,FALSE)

dates in column "A", names in column "B", no difference if they are other way around, add conditional formatting whilst having all range selected and active in cell "A1". it WILL be slow though....

Upvotes: 2

Related Questions