Spreadsheet: 815546d1675021941-remove-blank-cells-from-column-sorted-with-sortby-arrays-filters-problem
Sheet: Example

 |  | Monday | Tuesday | Wednesday | Thursday | Friday
 | Cleaning |  |  | Teal |  | Black
 |  | Teal | Violet |  | Black | Orange
 |  |  | Red | Violet | Yellow |  |  |  | Light green is a shift roster of sorts. Simplified here to serve the purpose.
 |  |  |  | Mauve |  | Teal
 |  | Violet
 |  |  | Mauve |  | Teal
 | Mess Duty
 |  |  | Lilac | Red | Violet | Yellow
 |  | Red | Orange |  | Red
 |  | Lilac |  | Lilac |  | Violet
 |  |  | Green | Orange | Mauve | Red
 |  |  |  |  |  |  |  |  | Each blue column in this range removes the empty cells between the filled cells in the green rows above it | "=FILTER(Green_Mon;Green_Mon<>"";"")"
 |  | Violet | Red | Violet | Yellow | Orange
 |  | Red | Mauve | Mauve | Teal | Teal |  |  | The number of times each name occurs in this whole blue range, is given in the red column below | "=IF(ISTEXT($C23);COUNTIF(Blue_Week;$C23);"")"
 |  | Lilac | Lilac | Red | Violet | Yellow
 |  |  | Orange | Lilac | Red | Violet
 |  |  | Green | Orange | Mauve | Red
 |  |  |  |  |  | GET | WANT
 |  | Black |  |  |  |  | Red |  | The yellow column is a list of all current staff members
 |  | Blue |  |  |  | 0 | Violet
 |  | Yellow |  |  |  | 0 | Teal |  | The list in the red column counts the occurrences of all the names in the range above | "=IF(ISTEXT($C23);COUNTIF(Blue_Week;$C23);"")"
 |  | Green |  |  |  | 0 | Orange
 |  | Red |  |  |  | Red | Lilac |  | The list in the pink column removes the names of staff members that do not occur at all in the blue range | "=IF(NOT(D23<>0);"";C23)"
 |  | Orange |  |  |  | Violet | Mauve
 |  | Violet |  |  |  | Teal | Black |  | The list in the navy column sorts the list in the pink column according to the number of occurrences of | "=SORTBY(Pink_List;Red_List;-1)"
 |  | Lilac |  |  |  | Orange | Yellow |  | staff member names, from most to least.
 |  | Chartreuse |  |  |  | Lilac
 |  | Teal |  |  |  | Mauve |  |  | The list in the green column shows the result I want. | "=?????????"
 |  | Mauve |  |  |  | Black
 |  | White |  |  |  | Yellow
 |  |  |  |  |  | Green
 |  |  |  |  |  |  |  |  | I would like to remove the spaces from the rightmost list (in navy) so that the list starts at the top. A direct way (pink to navy) or an indirect way (red and pink to orange to navy).  I've thought of filtering out the spaces first, but I cannot figure out how to remove spaces from the red list (I assume the cells need to be truly empty; not zero or something else). Also, I'm worried that spillover filtered ranges that differ in size may make further calculations more challenging.
 |  |  | 0 | Yellow
 |  |  | 2 | Green
 |  |  | 1 | Red
 |  |  | 5 | Orange |  |  |  |  |  | ORANGE LISTS :
 |  |  | 3 | Violet |  |  |  |  |  | "=FILTER(Red_List;Red_List<>"";"")" - doesn't work
 |  |  | 5 | Lilac |  |  |  |  |  | "=FILTER(Pink_List;Pink_List<>"";"")"- works
 |  |  | 3 | Teal
 |  |  | 0 | Mauve
 |  |  | 4 | 0
 |  |  | 3 | 0
 |  |  | 0 | 0
 |  |  |  | 0
 |  |  |  |  | Violet
 |  |  |  |  | Teal
 |  |  |  |  | Orange
 |  |  |  |  | Lilac
 |  |  |  |  | Mauve
 |  |  |  |  | Black
 |  |  |  |  | Yellow
 |  |  |  |  | Green
 |  |  |  |  |  | 5
 |  |  |  |  | Violet | 5
 |  |  |  |  | Teal | 4
 |  |  |  |  | Orange | 3
 |  |  |  |  | Lilac | 3
 |  |  |  |  | Mauve | 3
 |  |  |  |  | Black | 2
 |  |  |  |  | Yellow | 2
 |  |  |  |  | Green | 1
 |  |  |  |  | Violet
 |  |  |  |  | Teal
 |  |  |  |  | Orange
 |  |  |  |  | Lilac
 |  |  |  |  | Mauve
 |  |  |  |  | Black
 |  |  |  |  | Yellow
 |  |  |  |  | Green
 |  |  |  |  |  | 5
 |  |  |  |  | Violet | 5
 |  |  |  |  | Teal | 4
 |  |  |  |  | Orange | 3
 |  |  |  |  | Lilac | 3
 |  |  |  |  | Mauve | 3
 |  |  |  |  | Black | 2
 |  |  |  |  | Yellow | 2
 |  |  |  |  | Green | 1
 |  |  |  |  | Violet
 |  |  |  |  | Teal
 |  |  |  |  | Orange
 |  |  |  |  | Lilac
 |  |  |  |  | Mauve
 |  |  |  |  | Black
 |  |  |  |  | Yellow
 |  |  |  |  | Green