TEXTJOIN over multiple rows and 3 columns with specific date range

[Allen_Mead]

I want to be able to use the textjoin function to pull data from our CPD table into a summary for the working week, driven by the date on a given Monday (this will be in a separate cell)

The CPD table will be constantly updated and will include old data (as need to keep a record of all undertaken) and future information.

I would like the data to display as a weekday

Really stuck on this, screen shot attached (sorry i cannot install the XL2BB add on as I'm on a work computer) Many thanks for any help!

 

[Fluff]

You're welcome.

 

[Allen_Mead]

Had a thought, is it possible for the formula to sort the data chronologically? Asking as if people enter information in the CPD table in any order (which is fine) it will show the results in the cell chronologically correct?

At the moment, as an example, I have it that next week, there are 3 items on, Monday, Tuesday and Thursday. The formula currently picks everything up but, in the order that it's been entered in the table so we currently have events in this order; Tuesday, Thursday, Monday. Trying to ensure the data is 'forced' to show correctly even if people cannot be bothered to enter it correctly.

Thanks!

 

[Allen_Mead]

I assume that this would be using the SORT() or SORTBY() function but don't know where I should insert this in the formula

 

[felixstraube]

.

 

[felixstraube]

how about?:

Book1
	A	B	C	D	E	F
1	08/01/2024	Current date
2
3
4	Title	Where?	Date
5	Protecting High-rise Residential Buildings from Fire	webinar	12/01/2024
6	Built Environment Monthly Webinar - The Golden Thread of information	in person	08/01/2024
7	Bridging the gap between fire and emergency lighting systems	webinar	09/01/2024
8	Bureau Veritas - Building Safety Bill Seminar	webinar	10/01/2024
9	Class 0 Conumdrum	in person	15/01/2024
10	The Key Essentials of Water Mist Fire Protection	in person	16/01/2024
11						Monday: (in person) Built Environment Monthly Webinar - The Golden Thread of information Tuesday: (webinar) Bridging the gap between fire and emergency lighting systems Wednesday: (webinar) Bureau Veritas - Building Safety Bill Seminar Friday: (webinar) Protecting High-rise Residential Buildings from Fire
Sheet1

Cell Formulas
Range	Formula
F11	F11	=LET(d,$A$1, startOfWeek,WORKDAY.INTL(d+1,-1,"1111110"), endOfWeek,startOfWeek+6, events,FILTER(SORTBY(Tabla1,Tabla1[Date]),(Tabla1[Date]>=startOfWeek)*(Tabla1[Date]<=endOfWeek)), TEXTJOIN(CHAR(10),,BYROW(events,LAMBDA(x,TEXT(CHOOSECOLS(x,3),"dddd")&": ("&CHOOSECOLS(x,2)&") "&CHOOSECOLS(x,1)))) )

 

[felixstraube]

Or if you don't have you data as a table:

Book1
	A	B	C	D	E	F
1	08/01/2024	Current date
2
3
4	Title	Where?	Date
5	Protecting High-rise Residential Buildings from Fire	webinar	12/01/2024
6	Built Environment Monthly Webinar - The Golden Thread of information	in person	08/01/2024
7	Bridging the gap between fire and emergency lighting systems	webinar	09/01/2024
8	Bureau Veritas - Building Safety Bill Seminar	webinar	10/01/2024
9	Class 0 Conumdrum	in person	15/01/2024
10	The Key Essentials of Water Mist Fire Protection	in person	16/01/2024
11						Monday: (in person) Built Environment Monthly Webinar - The Golden Thread of information Tuesday: (webinar) Bridging the gap between fire and emergency lighting systems Wednesday: (webinar) Bureau Veritas - Building Safety Bill Seminar Friday: (webinar) Protecting High-rise Residential Buildings from Fire
Sheet1

Cell Formulas
Range	Formula
F11	F11	=LET(d,$A$1, startOfWeek,WORKDAY.INTL(d+1,-1,"1111110"), endOfWeek,startOfWeek+6, events,FILTER(SORTBY(Sheet1!$A$5:$C$10,Sheet1!$C$5:$C$10),(Sheet1!$C$5:$C$10>=startOfWeek)*(Sheet1!$C$5:$C$10<=endOfWeek)), TEXTJOIN(CHAR(10),,BYROW(events,LAMBDA(x,TEXT(CHOOSECOLS(x,3),"dddd")&": ("&CHOOSECOLS(x,2)&") "&CHOOSECOLS(x,1)))) )

 

[Allen_Mead]

Or if you don't have you data as a table:

Book1
	A	B	C	D	E	F
1	08/01/2024	Current date
2
3
4	Title	Where?	Date
5	Protecting High-rise Residential Buildings from Fire	webinar	12/01/2024
6	Built Environment Monthly Webinar - The Golden Thread of information	in person	08/01/2024
7	Bridging the gap between fire and emergency lighting systems	webinar	09/01/2024
8	Bureau Veritas - Building Safety Bill Seminar	webinar	10/01/2024
9	Class 0 Conumdrum	in person	15/01/2024
10	The Key Essentials of Water Mist Fire Protection	in person	16/01/2024
11						Monday: (in person) Built Environment Monthly Webinar - The Golden Thread of information Tuesday: (webinar) Bridging the gap between fire and emergency lighting systems Wednesday: (webinar) Bureau Veritas - Building Safety Bill Seminar Friday: (webinar) Protecting High-rise Residential Buildings from Fire
Sheet1

Cell Formulas
Range	Formula
F11	F11	=LET(d,$A$1, startOfWeek,WORKDAY.INTL(d+1,-1,"1111110"), endOfWeek,startOfWeek+6, events,FILTER(SORTBY(Sheet1!$A$5:$C$10,Sheet1!$C$5:$C$10),(Sheet1!$C$5:$C$10>=startOfWeek)*(Sheet1!$C$5:$C$10<=endOfWeek)), TEXTJOIN(CHAR(10),,BYROW(events,LAMBDA(x,TEXT(CHOOSECOLS(x,3),"dddd")&": ("&CHOOSECOLS(x,2)&") "&CHOOSECOLS(x,1)))) )

That works perfectly! Thank you.

 

[Fluff]

Another option

=TEXTJOIN(CHAR(10),,BYROW(SORT(A5:C10,3),LAMBDA(br,IF(AND(INDEX(br,,3)-A1>=0,INDEX(br,,3)-A1<7),TEXTJOIN({": (",") "},,TEXT(INDEX(br,,3),"dddd"),CHOOSECOLS(br,2,1)),""))))

 

[Allen_Mead]

Another option

=TEXTJOIN(CHAR(10),,BYROW(SORT(A5:C10,3),LAMBDA(br,IF(AND(INDEX(br,,3)-A1>=0,INDEX(br,,3)-A1<7),TEXTJOIN({": (",") "},,TEXT(INDEX(br,,3),"dddd"),CHOOSECOLS(br,2,1)),""))))

Works perfectly! Thank you!

 

[Fluff]

Glad we could help & thanks for the feedback.