Highlight duplicate values with conditions

[messa57fr]

Hi everyone,

Thanks for reading my post, I'm looking to highlight duplicate values within 24 hours from the previous duplicate value.

I'm using this but this is not what I want, I would like to replace NOW() with the timestamp of the previous duplicated value (if exist)
=AND(J2>=NOW()-1,COUNTIF(B:B,B2)>1)

My time stamp looks like that: 1/12/2022 08:05:47

Not sure if it's clear enough, but let me know. Thanks!
Sam

 

[Legacy 143009]

Like this?
=AND(COUNTIFS(J:J,J2,J:J,">="&NOW()-1)>1,COUNTIF(B:B,B2)>1)

 

[messa57fr]

Hi Flash,

I don't think there's should be NOW in the formula because I'm just looking at the time stamp of the previous duplicate value if it exists
This way I only have the oldest value valid by a cycle of 24 hours.

 

[Legacy 143009]

Then replace NOW() with J2 since is it your referance value.

 

[messa57fr]

I'm really sorry my explanation wasn't clear.
Here is a Screenshot to help out.

It's more complicated than I thought I believe.

 

[Legacy 143009]

Why row 5 is False? It is the first occurrence for c@gamail.

 

[messa57fr]

Yes it should be TRUE, it's just the actual formula that doesn't work

 

[Legacy 143009]

Could please take a screenshot with the actual values you want?

 

[messa57fr]

Here is exactly how it should work, again so sorry for the confusion.
Thanks!

 

[Peter_SSs]

See if this would work for you. Notice that the ranges end one row below the last data.

22 12 01.xlsm
	B								J	K
1
2	b								3/12/2022 22:05:00	TRUE
3	a								2/12/2022 10:05:00	TRUE
4	a								1/12/2022 22:03:00	FALSE
5	a								1/12/2022 08:05:00	FALSE
6	c								1/12/2022 08:05:00	TRUE
7	b								1/12/2022 08:00:00	TRUE
8	a								1/12/2022 08:00:00	TRUE
9
Dupe in 24hr

Cell Formulas
Range	Formula
K2:K8	K2	=J2-IFERROR(AGGREGATE(14,6,J3:J$9/((B3:B$9=B2)*(K3:K$9)),1),0)>1