auzanoo
Board Regular
- Joined
- Jan 6, 2020
- Messages
- 63
- Office Version
- 2007
- Platform
- Windows
dear experts,
could i get a vba code for this problem?
i have a list of movie titles:
and i have raw data:
and i want to be like this:
notes:
1. i just want to show the data from the movie list
2. the raw data movie title has a code
3. if the time is split, must be merged and use summary date (the blue one) ex: hotline 666
4. but if they split too long (more than one hour), keep them separated and ignore summary date ex: hattrick
5. format time from hh:mm:ss to hh:mm
big thanks if you could help this out. cause the real data has so many lists?
could i get a vba code for this problem?
i have a list of movie titles:
Movie Summary.xlsx | |||
---|---|---|---|
A | |||
1 | TITLE | ||
2 | YOU | ||
3 | 5 MAN: RISE OF THE ZOMBIES | ||
4 | DO NOT ENTER | ||
5 | HOTLINE 666 | ||
6 | HATTRICK | ||
List |
and i have raw data:
Movie Summary.xlsx | ||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | |||
1 | Market | Target | Non | |||||||||||||
2 | All | Total | Non | |||||||||||||
3 | Counter | Description (grouped) | Year | Channel | Day of week | Date | Time | A | B | C | D | E | F | G | ||
4 | 1 | 6V YOU | 2015 | CSTV | Sunday | 29/11/2015 | 25:42:38 - 25:59:59 | 186 | 0,4 | 12,2 | 0,4 | 309 | 10 | 60,1 | ||
5 | 2 | Summary 29/11/2015 | 186 | 0,4 | 12,2 | 0,4 | 309 | 10 | 60,1 | |||||||
6 | 3 | 2018 | CSTV | Saturday | 06/01/2018 | 12:33:53 - 13:58:32 | 647 | 1,3 | 9,7 | 1,9 | 3.256 | 17 | 19,9 | |||
7 | 4 | 14:02:09 - 15:06:51 | 632 | 1,3 | 9,8 | 1,9 | 2.449 | 17 | 25,8 | |||||||
8 | 5 | Summary 06/01/2018 | 640 | 1,3 | 9,7 | 1,9 | 2.907 | 17 | 22,4 | |||||||
9 | 6 | 6K HOTLINE 666 | 2015 | WTV | Thursday | 01/01/2015 | 09:56:12 - 11:05:04 | 986 | 1,9 | 12,8 | 3,1 | 4.532 | 15 | 21,8 | ||
10 | 7 | 11:05:05 - 11:10:01 | 867 | 1,6 | 14,7 | 2,2 | 3.148 | 19 | 27,6 | |||||||
11 | 7 | 11:10:01 - 11:56:15 | 1.191 | 2,3 | 15,4 | 3,2 | 3.621 | 15 | 32,9 | |||||||
12 | 8 | Summary 01/01/2015 | 1.068 | 2,1 | 13,9 | 3,2 | 4.166 | 15 | 26,2 | |||||||
13 | 9 | 6V WHY ALWAYS ME | 2017 | BTV | Friday | 06/10/2017 | 12:48:47 - 14:43:41 | 806 | 1,5 | 11,6 | 2,4 | 3.910 | 24 | 20,6 | ||
14 | 10 | Summary 06/10/2017 | 806 | 1,5 | 11,6 | 2,4 | 3.910 | 24 | 20,6 | |||||||
15 | 11 | 6I HATTRICK | 2019 | NTV | Tuesday | 07/05/2019 | 02:00:00 - 02:12:59 | 726 | 1,5 | 10,3 | 2,1 | 1.873 | 13 | 38,8 | ||
16 | 12 | 25:42:38 - 25:59:59 | 751 | 1,5 | 10,9 | 2,1 | 2.212 | 19 | 33,9 | |||||||
17 | 13 | Summary 07/05/2019 | 742 | 1,5 | 10,7 | 2,1 | 2.087 | 17 | 35,7 | |||||||
Raw |
and i want to be like this:
Movie Summary.xlsx | |||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | |||
1 | |||||||||||||||||
2 | MOVIE SUMMARY | ||||||||||||||||
3 | All Demographics | ||||||||||||||||
4 | |||||||||||||||||
5 | No. | Title | Tahun Rilis | Chn | Day | Date | Time | A | B | C | D | E | F | G | |||
6 | 1 | 6V YOU | 2015 | CSTV | Sunday | 29/11/2015 | 25:42 - 25:59 | 186 | 0,4 | 12,2 | 0,4 | 309 | 10 | 60,1 | |||
7 | 6V YOU | 2018 | CSTV | Saturday | 06/01/2018 | 12:33 - 15:06 | 640 | 1,3 | 9,7 | 1,9 | 2.907 | 17 | 22,4 | ||||
8 | 2 | 6V HOTLINE 666 | 2015 | WTV | Thursday | 01/01/2015 | 09:56 - 11:56 | 1.068 | 2,1 | 13,9 | 3,2 | 4.166 | 15 | 26,2 | |||
9 | 3 | 6I HATTRICK | 2019 | NTV | Tuesday | 07/05/2019 | 02:00 - 02:12 | 726 | 1,5 | 10,3 | 2,1 | 1.873 | 13 | 38,8 | |||
10 | 6I HATTRICK | 2019 | NTV | Tuesday | 07/05/2019 | 25:42 - 25:59 | 751 | 1,5 | 10,9 | 2,1 | 2.212 | 19 | 33,9 | ||||
11 | |||||||||||||||||
Summary |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
G10 | Cell Value | duplicates | text | NO |
G8 | Cell Value | duplicates | text | NO |
G7 | Cell Value | duplicates | text | NO |
G7 | Cell Value | duplicates | text | NO |
G6 | Cell Value | duplicates | text | NO |
G11:G1048576,G5:G6 | Cell Value | duplicates | text | NO |
G9 | Cell Value | duplicates | text | NO |
notes:
1. i just want to show the data from the movie list
2. the raw data movie title has a code
3. if the time is split, must be merged and use summary date (the blue one) ex: hotline 666
4. but if they split too long (more than one hour), keep them separated and ignore summary date ex: hattrick
5. format time from hh:mm:ss to hh:mm
big thanks if you could help this out. cause the real data has so many lists?