List from Multiple sources

[Kazaard]

Hello,

I am looking to make a list across multiple tabs into one big list.

Column A contains a unique identifier (which is concat of date and name). The Column starts at Row 28 titled Index
Column C contains date.(there are multiple date of the same value) The Column starts at Row 28 titled Date
Column F contains name (there are multiple unique names, however its the same names each day) The Column starts at Row 28 titled Adviser

Copy of BCC Book In form.xlsm
	A	B	C	D	E	F
28	Index	Nr	Date	Day	Week	Adviser
29	44317Availiblity	0	01/05/2021	Saturday	Week 17	Availiblity
30	44317Agent1	1	01/05/2021	Saturday	Week 17	Agent1
31	44317Agent2	2	01/05/2021	Saturday	Week 17	Agent2
32	44317Agent3	3	01/05/2021	Saturday	Week 17	Agent3
33	44317Agent4	4	01/05/2021	Saturday	Week 17	Agent4
34	44317Agent5	5	01/05/2021	Saturday	Week 17	Agent5
35	44317Agent6	6	01/05/2021	Saturday	Week 17	Agent6
36	44317Agent7	7	01/05/2021	Saturday	Week 17	Agent7
37	44317Agent8	8	01/05/2021	Saturday	Week 17	Agent8
38	44317Agent9	9	01/05/2021	Saturday	Week 17	Agent9
39	44317Agent10	10	01/05/2021	Saturday	Week 17	Agent10
40	44317Agent11	11	01/05/2021	Saturday	Week 17	Agent11
41	44317Agent12	12	01/05/2021	Saturday	Week 17	Agent12
42	44317Agent13	13	01/05/2021	Saturday	Week 17	Agent13
43	44317Agent14	14	01/05/2021	Saturday	Week 17	Agent14
44	44317Agent15	15	01/05/2021	Saturday	Week 17	Agent15
45	44317Agent16	16	01/05/2021	Saturday	Week 17	Agent16
46	44317	17	01/05/2021	Saturday	Week 17
47	44317	18	01/05/2021	Saturday	Week 17
48	44317	19	01/05/2021	Saturday	Week 17
49	44317	20	01/05/2021	Saturday	Week 17
50	44318Availiblity	0	02/05/2021	Sunday	Week 17	Availiblity
51	44318Agent1	1	02/05/2021	Sunday	Week 17	Agent1
52	44318Agent2	2	02/05/2021	Sunday	Week 17	Agent2
53	44318Agent3	3	02/05/2021	Sunday	Week 17	Agent3
54	44318Agent4	4	02/05/2021	Sunday	Week 17	Agent4
55	44318Agent5	5	02/05/2021	Sunday	Week 17	Agent5
56	44318Agent6	6	02/05/2021	Sunday	Week 17	Agent6
57	44318Agent7	7	02/05/2021	Sunday	Week 17	Agent7
58	44318Agent8	8	02/05/2021	Sunday	Week 17	Agent8
59	44318Agent9	9	02/05/2021	Sunday	Week 17	Agent9
60	44318Agent10	10	02/05/2021	Sunday	Week 17	Agent10
Team1

Cell Formulas
Range	Formula
C29	C29	=B2
D29:D60	D29	=TEXT(C29,"dddd")
E29:E60	E29	=VLOOKUP(C29,List!$C$2:$D$372,2,FALSE)
C51:C60,C30:C49	C30	=C29
F46:F49	F46	=IFNA(VLOOKUP(B46,$B$4:$C$23,2,FALSE),"")
A29:A60	A29	=CONCAT(C29,F29)
B50:B60,F51:F60	B50	=B29
C50	C50	=C49+1

Event1
Column EA contains Booking 1 start time (e.g 08:00) The Column starts at Row 29 titled Start
Column EB contains Booking 1 end time (e.g 08:45) The Column starts at Row 29 titled End
Column EC contains Booking 2 start time (e.g 10:15) The Column starts at Row 29 titled Start
Column ED contains Booking 2 end time (e.g 11:00) The Column starts at Row 29 titled End
Column EE contains Booking 1 start time (e.g 12:45) The Column starts at Row 29 titled Start
Column EF contains Booking 1 end time (e.g 13:30) The Column starts at Row 29 titled End
Column EG contains Booking 2 start time (e.g 17:45) The Column starts at Row 29 titled Start
Column EH contains Booking 2 end time (e.g18:00) The Column starts at Row 29 titled End
Column EI contains Booking 2 start time (e.g empty) The Column starts at Row 29 titled Start
Column EJ contains Booking 2 end time (e.g empty) The Column starts at Row 29 titled End
Event 2
Is the same layout as Event 1 only trough cells FZ-GI
Event 3
Is the same layout ad Event 1 only trough cells HY-IH

Copy of BCC Book In form.xlsm
	EA	EB	EC	ED	EE	EF	EG	EH	EI	EJ
27	Booking 1		Booking 2		Booking 3		Booking 4		Booking 5
28
29	Start	End	Start	End	Start	End	Start	End	Start	End
30	8:00:00	8:45:00	10:15:00	11:00:00	12:45:00	13:30:00	17:45:00	18:00:00
31
32
33
34
35
36	15:00:00	15:45:00
37
38
39
40
41
42
43
44
45
46
47
48
49
50	Start	End	Start	End	Start	End	Start	End	Start	End
51	8:00:00	8:45:00	10:15:00	11:00:00	12:45:00	13:30:00	17:45:00	18:00:00
52
53
54
55
56
57	15:00:00	15:45:00
58
59
60
61
62
Team1

Cell Formulas
Range	Formula
EA30:EH30,EA57:EB57,EA52:EA56,EA58:EA62,EA51:EH51,EA36:EB36,EA31:EA35,EA37:EA49	EA30	=IFERROR(Kazaard(CL30:DZ30),"")
Dynamic array formulas.

Now the sheet will contain all of the dates for one month going all the way down for one Team of names. e.g all of the above is only for Team1

I need these bookings to be displayed in a separate sheet in a list format missing out all the blanks in the bookings like the below.

Copy of BCC Book In form.xlsm
	A	B	C	D	E
1	Name	Date	Event	Start	End
2	Agent1	07/05/2021	Event 1	08:00	08:00
3	Agent1	07/05/2021	Event 1	08:45	10:15
4	Agent1	07/05/2021	Event 1	11:00	12:45
5	Agent1	07/05/2021	Event 1	12:45	13:30
6	Agent1	07/05/2021	Event 1	17:45	18:00
7	Agent1	07/05/2021	Event 2	09:00	09:30
8	Agent1	07/05/2021	Event 3	12:00	13:00
9	Agent2	07/05/2021	Event 1	09:45	10:00
10	Agent2	07/05/2021	Event 1	12:00	13:00
11	Agent2	07/05/2021	Event 2	11:00	12:00
12	Agent2	07/05/2021	Event 3	16:00	17:00
13	Agent1	08/05/2021	Event 1	08:00	08:00
14	Agent1	08/05/2021	Event 1	08:45	10:15
15	Agent1	08/05/2021	Event 1	11:00	12:45
16	Agent1	08/05/2021	Event 1	12:45	13:30
17	Agent1	08/05/2021	Event 1	17:45	18:00
18	Agent1	08/05/2021	Event 2	09:00	09:30
19	Agent1	08/05/2021	Event 3	12:00	13:00
20	Agent2	08/05/2021	Event 1	09:45	10:00
21	Agent2	08/05/2021	Event 1	12:00	13:00
22	Agent2	08/05/2021	Event 2	11:00	12:00
23	Agent2	08/05/2021	Event 3	16:00	17:00
Sheet6

There will be multiple tabs e.g Team1,Team2,Team 3 ect and they all needs to idealy eventually feed into one list. Or 3 seperate lists for each event.

If anyone could help me with the above would really appreciate it for formulas or VBA would be great. Im not sure how to feed all the information I need into the list without doing a lot of manual adjustments and lookups, and I am sure there is a better way of doing this.

Thanks in advance

Kaz.