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!

 

[vladimiratanasiu]

Is this what you need?

Book1
	A	B	C	D
1	Protecting High-rise Residential Buildings from Fire	webinar	05/01/2024	Friday:(webinar)Protecting High-rise Residential Buildings from Fire
2	Built Environment Monthly Webinar - The Golden Thread c	In person	08/01/2024	Monday:(In person)Built Environment Monthly Webinar - The Golden Thread c
3	Bridging thegap between fire and emergency lighting systt	webinar	09/01/2024	Tuesday:(webinar)Bridging thegap between fire and emergency lighting systt
4	Bureau Veritas - BuildingSafety Bill Seminar	webinar	10/01/2024	Wednesday:(webinar)Bureau Veritas - BuildingSafety Bill Seminar
5	Class 0 Conumdrum	In person	15/01/2024	Monday:(In person)Class 0 Conumdrum
6	The Key Essentials of Water Mist Fire Protection	In person	16/01/2024	Tuesday:(In person)The Key Essentials of Water Mist Fire Protection
Sheet1

Cell Formulas
Range	Formula
D1:D6	D1	=CONCATENATE(TEXT(C1,"dddd"),":","(",B1,")",A1)

 

[felixstraube]

How about one of these?:
Im assuming that your data is formatted as a table.
From the formula in F5 you will get one row for each entry. From the formula in F11 you will get all concatenated with a linebreak character:

TaskPerWeek.xlsx
	A	B	C	D	E	F
1	Current date	08/01/2024
2
3
4	Title	Where?	Date
5	Protecting High-rise…	webinar	05/01/2024			Monday: (in person) Built Environment…
6	Built Environment…	in person	08/01/2024			Tuesday: (webinar) Bridging the gap…
7	Bridging the gap…	webinar	09/01/2024			Wednesday: (webinar) Bureau Veritas - …
8	Bureau Veritas - …	webinar	10/01/2024
9	Class 0 Conundrum	in person	15/01/2024
10	The Key Essentials…	in person	16/01/2024
11						Monday: (in person) Built Environment… Tuesday: (webinar) Bridging the gap… Wednesday: (webinar) Bureau Veritas - …
Hoja1

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

 

[Allen_Mead]

Is this what you need?

Book1
	A	B	C	D
1	Protecting High-rise Residential Buildings from Fire	webinar	05/01/2024	Friday:(webinar)Protecting High-rise Residential Buildings from Fire
2	Built Environment Monthly Webinar - The Golden Thread c	In person	08/01/2024	Monday:(In person)Built Environment Monthly Webinar - The Golden Thread c
3	Bridging thegap between fire and emergency lighting systt	webinar	09/01/2024	Tuesday:(webinar)Bridging thegap between fire and emergency lighting systt
4	Bureau Veritas - BuildingSafety Bill Seminar	webinar	10/01/2024	Wednesday:(webinar)Bureau Veritas - BuildingSafety Bill Seminar
5	Class 0 Conumdrum	In person	15/01/2024	Monday:(In person)Class 0 Conumdrum
6	The Key Essentials of Water Mist Fire Protection	In person	16/01/2024	Tuesday:(In person)The Key Essentials of Water Mist Fire Protection
Sheet1

Cell Formulas
Range	Formula
D1:D6	D1	=CONCATENATE(TEXT(C1,"dddd"),":","(",B1,")",A1)

Nearly.

All the data would need to be in a single cell, hence the TEXTJOIN function and would need to only include data from a specific date range, ie. from 08/01/2024 + 5 days

 

[Fluff]

Another option

Fluff.xlsm
	A	B	C	D
1	08/01/2024
2
3
4				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
5	Protecting High-rise Residential Buildings from Fire	webinar	05/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
Sheet5

Cell Formulas
Range	Formula
D4	D4	=TEXTJOIN(CHAR(10),,BYROW(A5:C10,LAMBDA(br,IF(WEEKNUM(INDEX(br,,3))=WEEKNUM(A1),TEXTJOIN({": (",") "},,TEXT(INDEX(br,,3),"dddd"),CHOOSECOLS(br,2,1)),""))))

 

[Allen_Mead]

Another option

Fluff.xlsm
	A	B	C	D
1	08/01/2024
2
3
4				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
5	Protecting High-rise Residential Buildings from Fire	webinar	05/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
Sheet5

Cell Formulas
Range	Formula
D4	D4	=TEXTJOIN(CHAR(10),,BYROW(A5:C10,LAMBDA(br,IF(WEEKNUM(INDEX(br,,3))=WEEKNUM(A1),TEXTJOIN({": (",") "},,TEXT(INDEX(br,,3),"dddd"),CHOOSECOLS(br,2,1)),""))))

Hit the nail on the head! thank you so much

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[felixstraube]

On a week where the year end/begins the formula in post 6 wont work properly. Because the week number for the first days will be 53 (or 54) and the rest will be 1.

29/12/2024 Sun	53
30/12/2024 Mon	53
31/12/2024 Tue	53
01/01/2025 Wed	1
02/01/2025 Thu	1
03/01/2025 Fri	1
04/01/2025 Sat	1

I tried to take that into account in the formulas in post 3.

 

[Fluff]

Good point, another option to correct that

=TEXTJOIN(CHAR(10),,BYROW(A5:C10,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]

Good point, another option to correct that

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

Perfecto! thank you.