ibmy
Board Regular
- Joined
- Mar 4, 2020
- Messages
- 106
- Office Version
- 2016
- Platform
- Windows
Dear Smartest Excelers,
Need help on formula in D3 or micro.
A sample from 90k row data :
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...
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...