Hi, Hope you all are doing great. I am in a dire need of someone's help in excel vba. I have two data files, ok. 1) RPM and 2) Test data.
In last available column in Test Data file I require rate from RPM data sheet. I am a learner of Excel Programming, as in, new to it. My search criteria is based on 1) Channel, 2) Data point in between start time and end time, and 3) Day of the week. Can anyone help me out for creating the excel macro. Its a large data cannot do it manually
Both data files are attached.
Thanking you in anticipation.
Test Data
RPM (Day of the Week):
In last available column in Test Data file I require rate from RPM data sheet. I am a learner of Excel Programming, as in, new to it. My search criteria is based on 1) Channel, 2) Data point in between start time and end time, and 3) Day of the week. Can anyone help me out for creating the excel macro. Its a large data cannot do it manually
Both data files are attached.
Thanking you in anticipation.
Test Data
Test Data.xlsx | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | |||
1 | Date | Time | Channel | Day | Category | TransmissionHour | TimeBand | Ad_Duration_in_Mins | Mins | Ad_Slot | commercialMarkID | |||
2 | 25.09.2020 | 19:02:51 | Entertainment | Friday | Cullinary | 19 | Access Primetim | 00:00:35 | 0.58 | Third | 2.00925E+14 | |||
3 | 25.09.2020 | 19:20:14 | Entertainment | Friday | Cullinary | 19 | Access Primetim | 00:00:35 | 0.58 | Second | 2.00925E+14 | |||
4 | 25.09.2020 | 20:27:24 | Entertainment | Friday | Cullinary | 20 | Primetime | 00:00:35 | 0.58 | Other | 2.00925E+14 | |||
5 | 25.09.2020 | 20:42:12 | Entertainment | Friday | Cullinary | 20 | Primetime | 00:00:15 | 0.25 | last | 2.00925E+14 | |||
6 | 25.09.2020 | 22:20:10 | Entertainment | Friday | Cullinary | 22 | Late Primetime | 00:00:35 | 0.58 | Second | 2.00925E+14 | |||
7 | 25.09.2020 | 22:27:08 | Entertainment | Friday | Cullinary | 22 | Late Primetime | 00:00:35 | 0.58 | Other | 2.00925E+14 | |||
8 | 26.09.2020 | 02:16:44 | Entertainment | Saturday | Cullinary | 2 | Night | 00:00:06 | 0.10 | 0 | 2.00926E+14 | |||
9 | 26.09.2020 | 02:19:30 | Entertainment | Saturday | Cullinary | 2 | Night | 00:00:06 | 0.10 | 0 | 2.00926E+14 | |||
10 | 26.09.2020 | 02:29:15 | Entertainment | Saturday | Cullinary | 2 | Night | 00:00:06 | 0.10 | 0 | 2.00926E+14 | |||
11 | 26.09.2020 | 02:31:51 | Entertainment | Saturday | Cullinary | 2 | Night | 00:00:06 | 0.10 | 0 | 2.00926E+14 | |||
12 | 26.09.2020 | 02:40:14 | Entertainment | Saturday | Cullinary | 2 | Night | 00:00:05 | 0.08 | 0 | 2.00926E+14 | |||
13 | 26.09.2020 | 02:42:45 | Entertainment | Saturday | Cullinary | 2 | Night | 00:00:06 | 0.10 | 0 | 2.00926E+14 | |||
14 | 26.09.2020 | 02:52:29 | Entertainment | Saturday | Cullinary | 2 | Night | 00:00:05 | 0.08 | 0 | 2.00926E+14 | |||
15 | 26.09.2020 | 02:55:07 | Entertainment | Saturday | Cullinary | 2 | Night | 00:00:06 | 0.10 | 0 | 2.00926E+14 | |||
16 | 26.09.2020 | 03:16:45 | Entertainment | Saturday | Cullinary | 3 | Night | 00:00:05 | 0.08 | 0 | 2.00926E+14 | |||
17 | 26.09.2020 | 03:19:16 | Entertainment | Saturday | Cullinary | 3 | Night | 00:00:05 | 0.08 | 0 | 2.00926E+14 | |||
18 | 26.09.2020 | 03:27:13 | Entertainment | Saturday | Cullinary | 3 | Night | 00:00:06 | 0.10 | 0 | 2.00926E+14 | |||
19 | 26.09.2020 | 03:29:24 | Entertainment | Saturday | Cullinary | 3 | Night | 00:00:06 | 0.10 | 0 | 2.00926E+14 | |||
20 | 26.09.2020 | 03:39:08 | Entertainment | Saturday | Cullinary | 3 | Night | 00:00:05 | 0.08 | 0 | 2.00926E+14 | |||
21 | 26.09.2020 | 03:42:25 | Entertainment | Saturday | Cullinary | 3 | Night | 00:00:06 | 0.10 | 0 | 2.00926E+14 | |||
22 | 26.09.2020 | 03:54:29 | Entertainment | Saturday | Cullinary | 3 | Night | 00:00:05 | 0.08 | 0 | 2.00926E+14 | |||
23 | 26.09.2020 | 03:57:00 | Entertainment | Saturday | Cullinary | 3 | Night | 00:00:06 | 0.10 | 0 | 2.00926E+14 | |||
24 | 26.09.2020 | 04:17:53 | Entertainment | Saturday | Cullinary | 4 | Night | 00:00:05 | 0.08 | 0 | 2.00926E+14 | |||
25 | 26.09.2020 | 04:20:14 | Entertainment | Saturday | Cullinary | 4 | Night | 00:00:06 | 0.10 | 0 | 2.00926E+14 | |||
26 | 26.09.2020 | 04:28:46 | Entertainment | Saturday | Cullinary | 4 | Night | 00:00:05 | 0.08 | 0 | 2.00926E+14 | |||
27 | 26.09.2020 | 04:30:59 | Entertainment | Saturday | Cullinary | 4 | Night | 00:00:06 | 0.10 | 0 | 2.00926E+14 | |||
28 | 26.09.2020 | 04:42:22 | Entertainment | Saturday | Cullinary | 4 | Night | 00:00:05 | 0.08 | 0 | 2.00926E+14 | |||
29 | 26.09.2020 | 04:45:08 | Entertainment | Saturday | Cullinary | 4 | Night | 00:00:06 | 0.10 | 0 | 2.00926E+14 | |||
30 | 26.09.2020 | 04:52:56 | Entertainment | Saturday | Cullinary | 4 | Night | 00:00:05 | 0.08 | 0 | 2.00926E+14 | |||
31 | 26.09.2020 | 04:55:51 | Entertainment | Saturday | Cullinary | 4 | Night | 00:00:06 | 0.10 | 0 | 2.00926E+14 | |||
32 | 26.09.2020 | 05:10:53 | Entertainment | Saturday | Cullinary | 5 | Night | 00:00:05 | 0.08 | 0 | 2.00926E+14 | |||
33 | 26.09.2020 | 05:13:21 | Entertainment | Saturday | Cullinary | 5 | Night | 00:00:06 | 0.10 | 0 | 2.00926E+14 | |||
34 | 26.09.2020 | 05:23:21 | Entertainment | Saturday | Cullinary | 5 | Night | 00:00:05 | 0.08 | 0 | 2.00926E+14 | |||
35 | 26.09.2020 | 05:25:19 | Entertainment | Saturday | Cullinary | 5 | Night | 00:00:06 | 0.10 | 0 | 2.00926E+14 | |||
36 | 26.09.2020 | 05:38:54 | Entertainment | Saturday | Cullinary | 5 | Night | 00:00:05 | 0.08 | 0 | 2.00926E+14 | |||
37 | 26.09.2020 | 05:41:12 | Entertainment | Saturday | Cullinary | 5 | Night | 00:00:06 | 0.10 | 0 | 2.00926E+14 | |||
38 | 26.09.2020 | 05:52:08 | Entertainment | Saturday | Cullinary | 5 | Night | 00:00:05 | 0.08 | 0 | 2.00926E+14 | |||
39 | 26.09.2020 | 05:54:05 | Entertainment | Saturday | Cullinary | 5 | Night | 00:00:06 | 0.10 | 0 | 2.00926E+14 | |||
40 | 26.09.2020 | 06:08:09 | Entertainment | Saturday | Cullinary | 6 | Early Morning | 00:00:05 | 0.08 | 0 | 2.00926E+14 | |||
41 | 26.09.2020 | 06:08:37 | Entertainment | Saturday | Cullinary | 6 | Early Morning | 00:00:05 | 0.08 | 0 | 2.00926E+14 | |||
42 | 26.09.2020 | 06:21:58 | Entertainment | Saturday | Cullinary | 6 | Early Morning | 00:00:05 | 0.08 | 0 | 2.00926E+14 | |||
43 | 26.09.2020 | 06:22:25 | Entertainment | Saturday | Cullinary | 6 | Early Morning | 00:00:06 | 0.10 | 0 | 2.00926E+14 | |||
44 | 26.09.2020 | 06:33:44 | Entertainment | Saturday | Cullinary | 6 | Early Morning | 00:00:05 | 0.08 | 0 | 2.00926E+14 | |||
45 | 26.09.2020 | 06:34:12 | Entertainment | Saturday | Cullinary | 6 | Early Morning | 00:00:05 | 0.08 | 0 | 2.00926E+14 | |||
46 | 26.09.2020 | 06:55:28 | Entertainment | Saturday | Cullinary | 6 | Early Morning | 00:00:05 | 0.08 | 0 | 2.00926E+14 | |||
Test Data |
RPM (Day of the Week):
RPM (Day of the Week).xlsx | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | |||
1 | Start time | End Time | Channel | Monday | Tuesday | Wednesday | Thursday | Friday | Saturday | Sunday | ||
2 | 00:00:00 | 00:30:00 | Entertainment | 5300 | 5300 | 5300 | 5300 | 5300 | 5300 | 5300 | ||
3 | 00:30:00 | 01:00:00 | Entertainment | 5300 | 5300 | 5300 | 5300 | 5300 | 5300 | 5300 | ||
4 | 01:00:00 | 01:30:00 | Entertainment | 5300 | 5300 | 5300 | 5300 | 5300 | 5300 | 5300 | ||
5 | 01:30:00 | 02:00:00 | Entertainment | 5300 | 5300 | 5300 | 5300 | 5300 | 5300 | 5300 | ||
6 | 02:00:00 | 02:30:00 | Entertainment | 1475 | 1475 | 1475 | 1475 | 1475 | 1475 | 1475 | ||
7 | 02:30:00 | 03:00:00 | Entertainment | 1475 | 1475 | 1475 | 1475 | 1475 | 1475 | 1475 | ||
8 | 03:00:00 | 03:30:00 | Entertainment | 1475 | 1475 | 1475 | 1475 | 1475 | 1475 | 1475 | ||
9 | 03:30:00 | 04:00:00 | Entertainment | 1475 | 1475 | 1475 | 1475 | 1475 | 1475 | 1475 | ||
10 | 04:00:00 | 04:30:00 | Entertainment | 1475 | 1475 | 1475 | 1475 | 1475 | 1475 | 1475 | ||
11 | 04:30:00 | 05:00:00 | Entertainment | 1475 | 1475 | 1475 | 1475 | 1475 | 1475 | 1475 | ||
12 | 05:00:00 | 05:30:00 | Entertainment | 1475 | 1475 | 1475 | 1475 | 1475 | 1475 | 1475 | ||
13 | 05:30:00 | 06:00:00 | Entertainment | 1475 | 1475 | 1475 | 1475 | 1475 | 1475 | 1475 | ||
14 | 06:00:00 | 06:30:00 | Entertainment | 1475 | 1475 | 1475 | 1475 | 1475 | 1475 | 1475 | ||
15 | 06:30:00 | 07:00:00 | Entertainment | 1475 | 1475 | 1475 | 1475 | 1475 | 1475 | 1475 | ||
16 | 07:00:00 | 07:30:00 | Entertainment | 1475 | 1475 | 1475 | 1475 | 1475 | 1475 | 1475 | ||
17 | 07:30:00 | 08:00:00 | Entertainment | 1475 | 1475 | 1475 | 1475 | 1475 | 1475 | 1475 | ||
18 | 08:00:00 | 08:30:00 | Entertainment | 2750 | 2750 | 2750 | 2750 | 2750 | 2750 | 2750 | ||
19 | 08:30:00 | 09:00:00 | Entertainment | 2750 | 2750 | 2750 | 2750 | 2750 | 2750 | 2750 | ||
20 | 09:00:00 | 09:30:00 | Entertainment | 11250 | 11250 | 11250 | 11250 | 11250 | 11250 | 11250 | ||
21 | 09:30:00 | 10:00:00 | Entertainment | 11250 | 11250 | 11250 | 11250 | 11250 | 11250 | 11250 | ||
22 | 10:00:00 | 10:30:00 | Entertainment | 11250 | 11250 | 11250 | 11250 | 11250 | 11250 | 11250 | ||
23 | 10:30:00 | 11:00:00 | Entertainment | 11250 | 11250 | 11250 | 11250 | 11250 | 11250 | 11250 | ||
24 | 11:00:00 | 11:30:00 | Entertainment | 11250 | 11250 | 11250 | 11250 | 11250 | 11250 | 11250 | ||
25 | 11:30:00 | 12:00:00 | Entertainment | 11250 | 11250 | 11250 | 11250 | 11250 | 11250 | 11250 | ||
26 | 12:00:00 | 12:30:00 | Entertainment | 11250 | 11250 | 11250 | 11250 | 11250 | 11250 | 11250 | ||
27 | 12:30:00 | 13:00:00 | Entertainment | 11250 | 11250 | 11250 | 11250 | 11250 | 11250 | 11250 | ||
28 | 13:00:00 | 13:30:00 | Entertainment | 12000 | 12000 | 12000 | 12000 | 14000 | 14000 | 14000 | ||
29 | 13:30:00 | 14:00:00 | Entertainment | 12000 | 12000 | 12000 | 12000 | 14000 | 14000 | 14000 | ||
30 | 14:00:00 | 14:30:00 | Entertainment | 12000 | 12000 | 12000 | 12000 | 14000 | 14000 | 14000 | ||
31 | 14:30:00 | 15:00:00 | Entertainment | 12000 | 12000 | 12000 | 12000 | 14000 | 14000 | 14000 | ||
32 | 15:00:00 | 15:30:00 | Entertainment | 12000 | 12000 | 12000 | 12000 | 14000 | 14000 | 14000 | ||
33 | 15:30:00 | 16:00:00 | Entertainment | 12000 | 12000 | 12000 | 12000 | 14000 | 14000 | 14000 | ||
34 | 16:00:00 | 16:30:00 | Entertainment | 12000 | 12000 | 12000 | 12000 | 14000 | 14000 | 14000 | ||
35 | 16:30:00 | 17:00:00 | Entertainment | 12000 | 12000 | 12000 | 12000 | 14000 | 14000 | 14000 | ||
36 | 17:00:00 | 17:30:00 | Entertainment | 12000 | 12000 | 12000 | 12000 | 14000 | 14000 | 14000 | ||
37 | 17:30:00 | 18:00:00 | Entertainment | 12000 | 12000 | 12000 | 12000 | 14000 | 14000 | 14000 | ||
38 | 18:00:00 | 18:30:00 | Entertainment | 18000 | 18000 | 18000 | 18000 | 18000 | 18000 | 18000 | ||
39 | 18:30:00 | 19:00:00 | Entertainment | 18000 | 18000 | 18000 | 18000 | 18000 | 18000 | 18000 | ||
40 | 19:00:00 | 19:30:00 | Entertainment | 38000 | 38000 | 38000 | 38000 | 38000 | 38000 | 38000 | ||
41 | 19:30:00 | 20:00:00 | Entertainment | 38000 | 38000 | 38000 | 38000 | 38000 | 38000 | 38000 | ||
42 | 20:00:00 | 20:30:00 | Entertainment | 192000 | 192000 | 192000 | 192000 | 240000 | 240000 | 240000 | ||
43 | 20:30:00 | 21:00:00 | Entertainment | 192000 | 192000 | 192000 | 192000 | 240000 | 240000 | 240000 | ||
44 | 21:00:00 | 21:30:00 | Entertainment | 103500 | 103500 | 103500 | 103500 | 103500 | 103500 | 103500 | ||
45 | 21:30:00 | 22:00:00 | Entertainment | 103500 | 103500 | 103500 | 103500 | 103500 | 103500 | 103500 | ||
46 | 22:00:00 | 22:30:00 | Entertainment | 46100 | 46100 | 46100 | 46100 | 46100 | 46100 | 46100 | ||
47 | 22:30:00 | 23:00:00 | Entertainment | 46100 | 46100 | 46100 | 46100 | 46100 | 46100 | 46100 | ||
48 | 23:00:00 | 23:30:00 | Entertainment | 30800 | 30800 | 30800 | 30800 | 30800 | 30800 | 30800 | ||
49 | 23:30:00 | 00:00:00 | Entertainment | 30800 | 30800 | 30800 | 30800 | 30800 | 30800 | 30800 | ||
RPM |