Dynamic Reporting Calendar in Excel

[nikhil0311]

Book1
	A	B	C	D	E	F	G
1	Input Data
2	Date	BD	Frequency	Activity
3	Friday, November 1, 2019	1	Weekly	Mgmt Report
4	Monday, November 4, 2019	4	Weekly	Revenue Report
5	Monday, November 4, 2019	4	Weekly	Trade Report
6	Tuesday, November 5, 2019	5	Bi Weekly	Forecasting
7	Tuesday, November 5, 2019	5	Bi Weekly	Income Statement
8	Wednesday, November 6, 2019	6	Weekly	Client Meeting
9	Thursday, November 7, 2019	7	Weekly	Mgmt Report APAC
10	Friday, November 8, 2019	8	Monthly	Budget Activity
11	Friday, November 8, 2019	8	Monthly	Budget Activity APAC
12	Friday, November 8, 2019	8	Monthly	Budget Activity EMEA
13	Friday, November 8, 2019	8	Monthly	Budget Activity CANADA
14
15
16	Output Final
17	Business day	Friday, November 1, 2019	Monday, November 4, 2019	Tuesday, November 5, 2019	Wednesday, November 6, 2019	Thursday, November 7, 2019	Friday, November 8, 2019
18		Mgmt Report	Revenue Report	Forecasting	Client Meeting	Mgmt Report APAC	Budget Activity
19			Trade Report	Income Statement			Budget Activity APAC
20							Budget Activity EMEA
21							Budget Activity CANADA
Sheet1

Range A2 to D13 is the input data where we have different dates and activities
What I am looking for is the activites to be listed out in different columns as per the date
for example if we look at the input data i.e. A4:A5, the data is november 4th Monday, based on this we are looking to get the output below cell C17
Thanks in advance. Please let me know if you havd any questions

 

[Fluff]

In B18 copied down & across
=IFERROR(INDEX($D$3:$D$13,AGGREGATE(15,6,(ROW($D$3:$D$13)-ROW($D$3)+1)/($A$3:$A$13=B$17),ROWS(B$18:B18))),"")

 

[nikhil0311]

Hi Fluff. Thanks a lot for your help. Appreciate the quick response. If i want the same output in the below calendar, how can i do that, could you please help?

Book1
	B	C	D	E	F	G	H
25	November
26	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
27						1	2
28						Mgmt Report
29
30
31
32
33	3	4	5	6	7	8	9
34		Revenue Report	Forecasting	Client Meeting	Mgmt Report APAC	Budget Activity
35		Trade Report	Income Statement			Budget Activity APAC
36						Budget Activity EMEA
37						Budget Activity CANADA
38
39	10	11	12	13	14	15	16
40
41
42
43
44
45	17	18	19	20	21	22	23
46
47
48
49
50
51	24	25	26	27	28	29	30
52
53
54
55
56
57
58
59
60
61
62
Sheet1

 

[Fluff]

Are the value in rows 27,33 etc actual dates, or just numbers?

 

[nikhil0311]

actual dates

 

[Fluff]

How about

Book1
	B	C	D	E	F	G	H
26	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
27						01/11/2019	02/11/2019
28						Mgmt Report
29
30
31
32
33	03/11/2019	04/11/2019	05/11/2019	06/11/2019	07/11/2019	08/11/2019	09/11/2019
34		Revenue Report	Forecasting	Client Meeting	Mgmt Report APAC	Budget Activity
35		Trade Report	Income Statement			Budget Activity APAC
36						Budget Activity EMEA
37						Budget Activity CANADA
38
Database

Cell Formulas
Range	Formula
B28	=IFERROR(INDEX($D$3:$D$13,AGGREGATE(15,6,(ROW($D$3:$D$13)-ROW($D$3)+1)/($A$3:$A$13=B$27),ROWS(B$28:B28))),"")
B34	=IFERROR(INDEX($D$3:$D$13,AGGREGATE(15,6,(ROW($D$3:$D$13)-ROW($D$3)+1)/($A$3:$A$13=B$33),ROWS(B$34:B34))),"")