Highlight Duplicates, but only if less than 24 hours old

Bortas

New Member
Joined
May 30, 2020
Messages
9
Office Version
  1. 2013
Platform
  1. Windows
Good day!

I'm running a simple spreadsheet:
-ColumnA: string input by user
-ColumnB: timestamp of when string was input (=IF(A2<>"", NOW(), "")

What I'm trying to do is have conditional formatting highlight duplicate values in Column A, but only if the timestamp is less than 24 hours old.

I know that highlighting duplicate values is: COUNTIF(A:A, A5)>1

And so I thought I'd try: =AND(IF(B:B, $B5>=NOW()-1),COUNTIF(A:A, A5)>1)
but that is not correct.

I feel like I'm close, but not quite there. Anyone have any tips?
Thanks!
-Bort
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,056
Office Version
  1. 2019
Platform
  1. Windows
That's what COUNTIFS is for ;)

=COUNTIFS(A:A,A5,B:B,">="&NOW()-1)
 

Bortas

New Member
Joined
May 30, 2020
Messages
9
Office Version
  1. 2013
Platform
  1. Windows
=COUNTIFS(A:A,A5,B:B,">="&NOW()-1)

Has highlighted all cells in the last 24 hours, not just the duplicate cells. Furthermore, if an instance happened in the last 24hours, and there is a duplicate that is OLDER than 24 hours, it highlights the old thing too.

I think your duplicate statement is missing the last little bit (>1), as the formula for just highlighting duplicates is: =COUNTIF(A:A, A5)>1

Older than 24 hours should have no formatting of any kind
Less than 24 hours should be highlighted only if cell in A is a duplicate of another item **within the last 24 hours**

Thank you for trying!
-Bort
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,056
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Sorry, I did it as a cell formula to count the records instead of a cf formula :eek: your original formula was closer

=AND($B5>=NOW()-1),COUNTIF(A:A, A5)>1)
 

Bortas

New Member
Joined
May 30, 2020
Messages
9
Office Version
  1. 2013
Platform
  1. Windows
Oh man, that's the ticket! Slight correction: =AND($B5>=NOW()-1,COUNTIF(A:A, A5)>1)

(Remove the ) after NOW()-1)

Thanks for your help!

-Bort
 

Bortas

New Member
Joined
May 30, 2020
Messages
9
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

You know, unfortunately, i'm figuring out that this formula doesn't quite do the trick. It did appear so, on initial testing, but I kept seeing some oddness I was having a hard time defining. Having been working with it for the last several weeks, I figured out the above formula ACTUALLY does the following behavior:
-Search column A for duplicates, highlight a cell if it is the duplicate of a cell in column A, but only if the item is less than 24 hours old.
(Specifically, if "Sally Smith" was entered twice, and one was over 24 hours old, only the recent one was highlighted, when the goal would be that neither would be)

Expected / hoped for behavior:
-Search column A within the last 24 hours only (determined in column b). Highlight duplicate values.

For example, if "Sally Smith" appears twice in column A, if both of those were entered in the last 24 hours, BOTH would be highlighted. If one or both was more than 24 hours old, NEITHER would be highlighted.

The goal is that the conditional formatting is only looking for duplicates that have occurred in the last 24 hours. And if the item duplicates something older than 24 hours, THEN IT DOESNT get highlighted or noted in an way.

VBA Code:
 =AND($B5>=NOW()-1,COUNTIF(A:A, A5)>1)

Then I would think the logic would look something like:
Code:
 If A < 24h old, Then, count duplicates in column A

Thanks for any ideas!
 

Bortas

New Member
Joined
May 30, 2020
Messages
9
Office Version
  1. 2013
Platform
  1. Windows
Hmmm, perhaps a nested if?
Code:
if(b>24h, countif(A duplicates))
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,056
Office Version
  1. 2019
Platform
  1. Windows
Going back to a variation of my first suggestion, try

=COUNTIFS(A:A,A5,B:B,">="&(NOW()-1))>1
 

Watch MrExcel Video

Forum statistics

Threads
1,114,661
Messages
5,549,287
Members
410,908
Latest member
Allen P
Top