Dynamic Custom filtered tables for a Schedule

Question

I have several questions about sorting and organizing a schedule in google sheets. I have found some work arounds to get most of what I want done but was hoping to get some input and maybe some ideas on ways to make it work better.

Problem - I have a CSV file I can generate for the work schedule of all the employees. It is not very coinvent to work with and look at. I am trying to take the data from the spreadsheet and sort it by day, department and Name.

1. Some of the people have split shifts which causes a second row with a blank cell where the name should be. (work around create another list that if the cell is blank it duplicate the data from the one above.)
2. Time and department for the shift are in the same cell and need to be separated (work around search every cell for all departments and display the name that matches and for the time take all the text left of the 12th character)
3. Lastly it would be nice to reverse the name so it is First then Last not "Last, First". As well as being able to have custom/nick names for example instead of Nunez, Elizabeth It would display Eli Nunez. This goes for the department was well. (Work around is similar to the department and just searching for every name and displaying the match with "=IF(ISNUMBER(SEARCH..." )

Ideally this would be done with a dynamic filter/pivot table/dynamic array that way there's not a lot of code in each cell and would automatically fill and sort.

I can also provide all my work around code if needed.

Sheet Example
"Full Week" sheet is what I am starting with and "Sorted by Day" sheet is the output/end goal I am looking for.

Hopefully this is enough information and sorry if this is the wrong place to ask/ post this.

Thank you
- Alan

Thank you for the help. I am still having a bit of trouble implementing it into my final schedule sheet. Here is the my final sheet with all of my workaround code.

Answer 1

try:

=INDEX(IFERROR(REGEXREPLACE(TRANSPOSE(SPLIT(TRIM(FLATTEN(QUERY(QUERY(SUBSTITUTE(
 TRIM(SPLIT(FLATTEN(QUERY(TRANSPOSE(REGEXREPLACE(REGEXREPLACE(TRIM(QUERY(SPLIT(FLATTEN(
 QUERY(TRANSPOSE(QUERY(SPLIT(FLATTEN(IF('Full Week'!B2:20="",,'Full Week'!B1:1&"♣"&
 TEXT('Full Week'!B1:1, "emmdd\♠ddd\♦")&"♣"&REGEXREPLACE(VLOOKUP(ROW('Full Week'!A2:A20), 
 IF('Full Week'!A2:A20<>"", {ROW('Full Week'!A2:A20), 'Full Week'!A2:A20}), 2, 1), 
 "(.*),(.*)", "♥$2 $1")&"♣♥"&SUBSTITUTE('Full Week'!B2:20, CHAR(10), "♣"))), "♣"), 
 "select Col2,Col5,Col3,Col4 
  where not Col4 matches '^♥ $' 
    and Col1 >= date '"&TEXT(TODAY()+1, "e-m-d")&"' 
    and Col1 <= date '"&TEXT(TODAY()+4, "e-m-d")&"'")),,9^9)), "♦"), 
 "select max(Col2) 
  group by Col2
  pivot Col1")), "^♥", "♂ ♥"), "^$", " ♥ ♥ ♥")&" ♥"),,9^9)), "♥")), " ", "♀"), 
 "offset 1", 0),,9^9))), " ")), "♀|♂", " ")))

domo sheet