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!

 

[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)),""))))

I'd like to add a 4th column in which I have done and works in the order I want to but, I have an additional opening bracket in front of the word "Fire" which I'd like to get rid of but can't quite work out how to adjust the delimiter to do so.

as be low and attached.

=TEXTJOIN(CHAR(10),,BYROW(SORT('CPD-Meetings'!A3:D300,4),LAMBDA(br,IF(AND(INDEX(br,,4)-D1>=0,INDEX(br,,4)-D1<7),TEXTJOIN({": (",") "},,TEXT(INDEX(br,,4),"dddd"),CHOOSECOLS(br,2,3,1)),""))))

 

[Fluff]

Maybe

=TEXTJOIN(CHAR(10),,BYROW(SORT('CPD-Meetings'!A3:D300,4),LAMBDA(br,IF(AND(INDEX(br,,4)-D1>=0,INDEX(br,,4)-D1<7),TEXTJOIN({": (",") "," "},,TEXT(INDEX(br,,4),"dddd"),CHOOSECOLS(br,2,3,1)),""))))

 

[Allen_Mead]

Maybe

=TEXTJOIN(CHAR(10),,BYROW(SORT('CPD-Meetings'!A3:D300,4),LAMBDA(br,IF(AND(INDEX(br,,4)-D1>=0,INDEX(br,,4)-D1<7),TEXTJOIN({": (",") "," "},,TEXT(INDEX(br,,4),"dddd"),CHOOSECOLS(br,2,3,1)),""))))

Perfect! Thank you again!

 

[Fluff]

You're welcome.

 

[Allen_Mead]

Sorry to be a pain, but, if I want to do this in reverse, i.e. only include from the past 7 days from a specific date, which bit do I change please?

 

[Fluff]

Maybe

=TEXTJOIN(CHAR(10),,BYROW(SORT('CPD-Meetings'!A3:D300,4),LAMBDA(br,IF(AND(INDEX(br,,4)>=D1-7,INDEX(br,,4)<=D1),TEXTJOIN({": (",") "," "},,TEXT(INDEX(br,,4),"dddd"),CHOOSECOLS(br,2,3,1)),""))))

 

[Allen_Mead]

Maybe

=TEXTJOIN(CHAR(10),,BYROW(SORT('CPD-Meetings'!A3:D300,4),LAMBDA(br,IF(AND(INDEX(br,,4)>=D1-7,INDEX(br,,4)<=D1),TEXTJOIN({": (",") "," "},,TEXT(INDEX(br,,4),"dddd"),CHOOSECOLS(br,2,3,1)),""))))

Looking good thank you. I do however seem to get an issue with the range in that where there are no entries to return, i get the repetitive 00/01/1900

 

[Allen_Mead]

Just realised, the formula is pulling greater than 7 days?

 

[Fluff]

the formula is pulling greater than 7 days?

Not the formula I posted.

 

[Allen_Mead]

Not the formula I posted.

Quite right, mistake on my part! Thank you again for your excel wizardry!