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

 

[Fluff]

You're welcome & thanks for the feedback.