Hi all,
i have device that records some data (Rain Gauge). Device works like this: if there is rain, it records intensity, and it writes it to that minute. After last activity it also writes five more datas that are zero.
My main problem is that a don't know how to write formula, or script to add rows between last zero value and first next value. I have data for thousands of rows, so manual work is a no go.
For example, this is my data:
And now i want to add 9 rows between 4:12 and 4:22. Also 12 rows between 4:27 to 4:40, and so on to look like this:
If this can be made, can you help me with next. When i add blank rows can i quickly fill blank times and date that it fits to data. And of course to add zero value to blank spots in C column.
It would look like this (yellow color is how data should be added):
Thanks for help.
i have device that records some data (Rain Gauge). Device works like this: if there is rain, it records intensity, and it writes it to that minute. After last activity it also writes five more datas that are zero.
My main problem is that a don't know how to write formula, or script to add rows between last zero value and first next value. I have data for thousands of rows, so manual work is a no go.
For example, this is my data:
K2_podaci_test.xlsx | |||||
---|---|---|---|---|---|
A | B | C | |||
1 | date | time | [mm/T] | ||
2 | 20.11.2020 | 4:07:00 | 0,1 | ||
3 | 20.11.2020 | 4:08:00 | 0 | ||
4 | 20.11.2020 | 4:09:00 | 0 | ||
5 | 20.11.2020 | 4:10:00 | 0 | ||
6 | 20.11.2020 | 4:11:00 | 0 | ||
7 | 20.11.2020 | 4:12:00 | 0 | ||
8 | 20.11.2020 | 4:22:00 | 0,1 | ||
9 | 20.11.2020 | 4:23:00 | 0 | ||
10 | 20.11.2020 | 4:24:00 | 0 | ||
11 | 20.11.2020 | 4:25:00 | 0 | ||
12 | 20.11.2020 | 4:26:00 | 0 | ||
13 | 20.11.2020 | 4:27:00 | 0 | ||
14 | 20.11.2020 | 4:40:00 | 0,1 | ||
15 | 20.11.2020 | 4:41:00 | 0 | ||
16 | 20.11.2020 | 4:42:00 | 0 | ||
17 | 20.11.2020 | 4:43:00 | 0 | ||
18 | 20.11.2020 | 4:44:00 | 0 | ||
19 | 20.11.2020 | 4:45:00 | 0 | ||
20 | 20.11.2020 | 4:51:00 | 0,1 | ||
21 | 20.11.2020 | 4:52:00 | 0 | ||
22 | 20.11.2020 | 4:53:00 | 0 | ||
23 | 20.11.2020 | 4:54:00 | 0 | ||
24 | 20.11.2020 | 4:55:00 | 0 | ||
25 | 20.11.2020 | 4:56:00 | 0 | ||
List1 |
And now i want to add 9 rows between 4:12 and 4:22. Also 12 rows between 4:27 to 4:40, and so on to look like this:
K2_podaci_test.xlsx | |||||
---|---|---|---|---|---|
A | B | C | |||
1 | date | time | [mm/T] | ||
2 | 20.11.2020 | 4:07:00 | 0,1 | ||
3 | 20.11.2020 | 4:08:00 | 0 | ||
4 | 20.11.2020 | 4:09:00 | 0 | ||
5 | 20.11.2020 | 4:10:00 | 0 | ||
6 | 20.11.2020 | 4:11:00 | 0 | ||
7 | 20.11.2020 | 4:12:00 | 0 | ||
8 | |||||
9 | |||||
10 | |||||
11 | |||||
12 | |||||
13 | |||||
14 | |||||
15 | |||||
16 | |||||
17 | 20.11.2020 | 4:22:00 | 0,1 | ||
18 | 20.11.2020 | 4:23:00 | 0 | ||
19 | 20.11.2020 | 4:24:00 | 0 | ||
20 | 20.11.2020 | 4:25:00 | 0 | ||
21 | 20.11.2020 | 4:26:00 | 0 | ||
22 | 20.11.2020 | 4:27:00 | 0 | ||
23 | |||||
24 | |||||
25 | |||||
26 | |||||
27 | |||||
28 | |||||
29 | |||||
30 | |||||
31 | |||||
32 | |||||
33 | |||||
34 | |||||
35 | 20.11.2020 | 4:40:00 | 0,1 | ||
36 | 20.11.2020 | 4:41:00 | 0 | ||
37 | 20.11.2020 | 4:42:00 | 0 | ||
38 | 20.11.2020 | 4:43:00 | 0 | ||
39 | 20.11.2020 | 4:44:00 | 0 | ||
40 | 20.11.2020 | 4:45:00 | 0 | ||
41 | 20.11.2020 | 4:51:00 | 0,1 | ||
42 | 20.11.2020 | 4:52:00 | 0 | ||
43 | 20.11.2020 | 4:53:00 | 0 | ||
44 | 20.11.2020 | 4:54:00 | 0 | ||
45 | 20.11.2020 | 4:55:00 | 0 | ||
46 | 20.11.2020 | 4:56:00 | 0 | ||
List1 |
If this can be made, can you help me with next. When i add blank rows can i quickly fill blank times and date that it fits to data. And of course to add zero value to blank spots in C column.
It would look like this (yellow color is how data should be added):
K2_podaci_test.xlsx | |||||
---|---|---|---|---|---|
A | B | C | |||
1 | date | time | [mm/T] | ||
2 | 20.11.2020 | 4:07:00 | 0,1 | ||
3 | 20.11.2020 | 4:08:00 | 0 | ||
4 | 20.11.2020 | 4:09:00 | 0 | ||
5 | 20.11.2020 | 4:10:00 | 0 | ||
6 | 20.11.2020 | 4:11:00 | 0 | ||
7 | 20.11.2020 | 4:12:00 | 0 | ||
8 | 20.11.2020 | 4:13:00 | 0 | ||
9 | 20.11.2020 | 4:14:00 | 0 | ||
10 | 20.11.2020 | 4:15:00 | 0 | ||
11 | 20.11.2020 | 4:16:00 | 0 | ||
12 | 20.11.2020 | 4:17:00 | 0 | ||
13 | 20.11.2020 | 4:18:00 | 0 | ||
14 | 20.11.2020 | 4:19:00 | 0 | ||
15 | 20.11.2020 | 4:20:00 | 0 | ||
16 | 20.11.2020 | 4:21:00 | 0 | ||
17 | 20.11.2020 | 4:22:00 | 0,1 | ||
18 | 20.11.2020 | 4:23:00 | 0 | ||
19 | 20.11.2020 | 4:24:00 | 0 | ||
20 | 20.11.2020 | 4:25:00 | 0 | ||
21 | 20.11.2020 | 4:26:00 | 0 | ||
22 | 20.11.2020 | 4:27:00 | 0 | ||
23 | 20.11.2020 | 4:28:00 | 0 | ||
24 | 20.11.2020 | 4:29:00 | 0 | ||
25 | 20.11.2020 | 4:30:00 | 0 | ||
26 | 20.11.2020 | 4:31:00 | 0 | ||
27 | 20.11.2020 | 4:32:00 | 0 | ||
28 | 20.11.2020 | 4:33:00 | 0 | ||
29 | 20.11.2020 | 4:34:00 | 0 | ||
30 | 20.11.2020 | 4:35:00 | 0 | ||
31 | 20.11.2020 | 4:36:00 | 0 | ||
32 | 20.11.2020 | 4:37:00 | 0 | ||
33 | 20.11.2020 | 4:38:00 | 0 | ||
34 | 20.11.2020 | 4:39:00 | 0 | ||
35 | 20.11.2020 | 4:40:00 | 0,1 | ||
36 | 20.11.2020 | 4:41:00 | 0 | ||
37 | 20.11.2020 | 4:42:00 | 0 | ||
38 | 20.11.2020 | 4:43:00 | 0 | ||
39 | 20.11.2020 | 4:44:00 | 0 | ||
40 | 20.11.2020 | 4:45:00 | 0 | ||
List1 |
Thanks for help.