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.
- 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.)
- 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)
- 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.
