textjoin query

[scottwilliams]

i have been requested to make a simple absence viewer iin our office that is feeding from a report from our HR system and appending that report over the top of the previous one.

Essentially, the report comes down like this :

Name, Unique ID, Office Location, Dept and then absent date.

At the moment i am using VBA to transpose this data, and then using the Textjoin function once i have concatenated the name, office and dept fields to output a list like so :

{=IF(D$2=6,"Saturday",IF(D$2=7,"Sunday",TEXTJOIN(CHAR(10),TRUE,IF(Report!$I$7:$GE$7=D$4,Report!$I$2:$GE$2,""))))}

(obviously with some if functions to just highlight weekends as we are only interested in weekdays.

At first, i was not happy with the delimiter being a comma, so i added the CHAR(10) function for a line break, but my query... is more of a visual thing, is there any way to alphabetise the outputs the textjoin function provides? As the formula is joining based o ndates, these dates are not always in sequence and alphabetised in the source data, and even sorting them before they are transposed does not change the output. i amgetting something similar to the below :

London, Back Office, Joe Bloggs
Washington, HR, Jo Bloggs
Katmandu, Sales, Joseph Bloggs

Can anybody suggest a way? It's not important, it will just make the output a bit more visually appealing for the board

Thanks in advance

 

[Snypa]

Are you willing to use a VBA solution?