Number of Same Value Appear in a Range of Time

[ibmy]

Dear Smartest Excelers,

Need help on formula in D3 or micro.
A sample from 90k row data :

Time Group.xlsx
	A	B	C	D	E
1		time	value	10 seconds
2		00:05:42	90.777
3		00:08:42	90.111
4		00:08:46	90.111	1
5		00:10:14	93.333
6		00:12:25	90.001
7		00:12:25	90.001	1	> count , same second and in 10 seconds range before
8		00:17:55	92.212
9		00:18:01	92.212	1
10		00:18:11	90.112
11		00:27:14	89.987
12		22:53:42	89.987		> no count, same value but not in 10 seconds range before
13		22:53:53	89.987		> no count, same value but not in 10 seconds range before
14		22:54:51	91.207
15		22:58:37	90.01
16		23:39:39	94.004
17		23:39:42	93.333		> no count, time is in range of 10 seconds but not same value before
18		23:39:44	94.001
19		23:40:20	91.111
20		23:40:42	91.111		> no count, same value but not in 10 seconds range before
21		23:40:50	91.111	1
22		23:40:53	91.111	2
23		23:41:02	91.111	3
24		23:43:55	91.111		> no count, same value but not in 10 seconds range before
25		23:43:27	89.999
26
Sheet2

Rule: The value must be the same from previous row (Column C) & Time in 10 seconds range from previous row (Column B)

Hopefully, the formula/micro also can be modified to desire custom range : 1 minute (60seconds), 1 minute 30 seconds (90seconds),etc...

 

[FormR]

Hi, maybe you can give this a try.

Book1
	B	C	D
1	time	value	10 seconds
2	00:05:42	90.777
3	00:08:42	90.111
4	00:08:46	90.111	1
5	00:10:14	93.333
6	00:12:25	90.001
7	00:12:25	90.001	1
8	00:17:55	92.212
9	00:18:01	92.212	1
10	00:18:11	90.112
11	00:27:14	89.987
12	22:53:42	89.987
13	22:53:53	89.987
14	22:54:51	91.207
15	22:58:37	90.01
16	23:39:39	94.004
17	23:39:42	93.333
18	23:39:44	94.001
19	23:40:20	91.111
20	23:40:42	91.111
21	23:40:50	91.111	1
22	23:40:53	91.111	2
23	23:41:02	91.111	3
24	23:43:55	91.111
25	23:43:27	89.999
Sheet1

Cell Formulas
Range	Formula
D3:D25	D3	=IF(AND(C3=C2,B3-B2<TIME(0,0,10)),N(D2)+1,"")

 

[maabadi]

Use Criteria as seconds at cell G1:

TestFormatting.xlsm
	A	B	C	D	E	F	G
1		time	value	10 Seconds		Criteria(Seconds)	10
2		0:05:42	90.777
3		0:08:42	90.111
4		0:08:46	90.111	1
5		0:10:14	93.333
6		0:12:25	90.001
7		0:12:25	90.001	1
8		0:17:55	92.212
9		0:18:01	92.212	1
10		0:18:11	90.112
11		0:27:14	89.987
12		22:53:42	89.987
13		22:53:53	89.987
14		22:54:51	91.207
15		22:58:37	90.01
16		23:39:39	94.004
17		23:39:42	93.333
18		23:39:44	94.001
19		23:40:20	91.111
20		23:40:42	91.111
21		23:40:50	91.111	1
22		23:40:53	91.111	2
23		23:41:02	91.111	3
24		23:43:55	91.111
25		23:43:27	89.999
Sheet4

Cell Formulas
Range	Formula
D1	D1	= 10 & " Seconds"
D2:D25	D2	=IF(IFERROR(IF(AND(C2=C1,(B2-B1)<$G$1/86400),COUNTIFS($C$2:C2,C2,$B$2:B2,">="&B2-$G$1/86400)-1,""),"")="","",IFERROR(IF(AND(C2=C1,(B2-B1)<$G$1/86400),COUNTIFS($C$2:C2,C2,$B$2:B2,">="&B2-$G$1/86400)-1,""),"")+IF(D1="",0,D1))

 

[alz]

=COUNTIFS($C$2:$C$21,C2,$B$2:$B$21,">="&B2-0.00694444444444444,$B$2:$B$21,"<="&B2)-1

 

[FormR]

A sample from 90k row data

Be aware that with that many rows, any suggestion that involves COUNTIFS() is likely to be a big drain on Excels resources.