Textjoin / iferror and ifs

[Allen_Mead]

Hi all,

I am trying to pull data into a single cell from a table which has to days of the week on the horizontal axis and people on the vertical axis. The results depends if some is present on a particular day or not. I have text separators in the form of colons and semi colons. This is great but, I want to make them disappear if they are not needed for example:

Currently shows as:
Bob Jones: Monday, Aldgate: Tuesday, St Albans, ; , ; ,

I'd like to be able to get it to show as:
Bob Jones: Monday, Aldgate: Tuesday, St Albans,

Excel formula below. Thanks in advance. PS, if there's a better way/ shorter way with the formula, I'm all ears. - Why all this in a single cell, because it's a challenge!

=TEXTJOIN(CHAR(10),TRUE,'7.Attendance'!A4:A15&TEXT(,": ")&IFERROR(IFS(B4:B15="Aldgate",B2,B4:B15="St Albans",B2),"")&TEXT(,", ")&IFERROR(IFS(B4:B15="Aldgate","Aldgate",B4:B15="St Albans","At Albans"),"")&TEXT(,": ")&IFERROR(IFS(C4:C15="Aldgate",C2,C4:C15="St Albans",C2),"")&TEXT(,", ")&IFERROR(IFS(C4:C15="Aldgate","Aldgate",C4:C15="St Albans","St Albans"),"")&TEXT(,": ")&IFERROR(IFS(D4:D15="Aldgate",D2,D4:D15="St Albans",D2),"")&TEXT(,", ")&IFERROR(IFS(D4:D15="Aldgate","Aldgate",D4:D15="St Albans","St Albans"),"")&TEXT(,": ")&IFERROR(IFS(E4:E15="Aldgate",E2,E4:E15="St Albans",E2),"")&TEXT(,", ")&IFERROR(IFS(E4:E15="Aldgate","Aldgate",E4:E15="St Albans","St Albans"),"")&TEXT(,": ")&IFERROR(IFS(F4:F15="Aldgate",F2,F4:F15="St Albans",F2),"")&TEXT(,", ")&IFERROR(IFS(F4:F15="Aldgate","Aldgate",F4:F15="St Albans","St Albans"),""))

 

[Peter_SSs]

1. I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
2. There is a lot of typing in that to set up to test. Could we have some sample data with XL2BB so that we can easily copy for testing?
3. Why should it show "Bob Jones: Monday, Aldgate: Tuesday, St Albans,"? I can't see anything about St Albans in your sample.
4. Would be best if you manually entered all the expected results in your sheet and include that as well with your XL2BB sample.

 

[Fluff]

If you have 365 one option is

=TEXTJOIN(CHAR(10),,A4:A15&": "&BYROW(B4:F15,LAMBDA(br,TEXTJOIN(": ",,HSTACK(TOCOL(FILTER(B2:F2,(br="aldgate")+(br="st albans"),"")),TOCOL(FILTER(br,(br="aldgate")+(br="st albans"),"")))))))

 

[Allen_Mead]

If you have 365 one option is

=TEXTJOIN(CHAR(10),,A4:A15&": "&BYROW(B4:F15,LAMBDA(br,TEXTJOIN(": ",,HSTACK(TOCOL(FILTER(B2:F2,(br="aldgate")+(br="st albans"),"")),TOCOL(FILTER(br,(br="aldgate")+(br="st albans"),"")))))))

Sorry i meant to add that it is office 365 we have. I can't do the XL2BB at the moment as I'm on the work computer and these things are nailed right down.

That is absolutely mental. Thank you soo much. Very nicely trimmed and works perfectly!!!!. Would it be possible to insert a comma between the day of the week and location for each day such as Wednesday, Aldgate: Thursday, St Albans and retain the colon between each day?

Thanks again.

 

[Fluff]

i meant to add that it is office 365 we have

Please don't forget to update your profile with this.

How about

=TEXTJOIN(CHAR(10),,A4:A15&": "&BYROW(B4:F15,LAMBDA(br,TEXTJOIN({", ",": "},,HSTACK(TOCOL(FILTER(B2:F2,(br="aldgate")+(br="st albans"),"")),TOCOL(FILTER(br,(br="aldgate")+(br="st albans"),"")))))))

 

[Allen_Mead]

Please don't forget to update your profile with this.

How about

=TEXTJOIN(CHAR(10),,A4:A15&": "&BYROW(B4:F15,LAMBDA(br,TEXTJOIN({", ",": "},,HSTACK(TOCOL(FILTER(B2:F2,(br="aldgate")+(br="st albans"),"")),TOCOL(FILTER(br,(br="aldgate")+(br="st albans"),"")))))))

I take it you can't do things like make the people's names in bold?

 

[Fluff]

Not in a formula, no.

 

[Allen_Mead]

Hi, I have one further thing that I would like to do with this. If, someone doesn't fill out the table (i.e. no data entered for "Aldgate" or "St Albans") then there name doesn't appear in the list. Getting a little stuck on this. Thanks.

 

[Fluff]

How about

=LET(b,BYROW(B4:F15,LAMBDA(br,TEXTJOIN({", ",": "},,HSTACK(TOCOL(FILTER(B2:F2,(br="aldgate")+(br="st albans"),"")),TOCOL(FILTER(br,(br="aldgate")+(br="st albans"),"")))))),TEXTJOIN(CHAR(10),,FILTER(A4:A15&": "&b,b<>"","")))

 

[Allen_Mead]

=LET(b,BYROW(B4:F15,LAMBDA(br,TEXTJOIN({", ",": "},,HSTACK(TOCOL(FILTER(B2:F2,(br="aldgate")+(br="st albans"),"")),TOCOL(FILTER(br,(br="aldgate")+(br="st albans"),"")))))),TEXTJOIN(CHAR(10),,FILTER(A4:A15&": "&b,b<>"","")))

Again, amazing, works perfectly, thank you so much.