Dynamic arrays
TOCOL function: flatten a 2D schedule grid into a single clean column
Unroll a multi-column table into one vertical list with TOCOL, remove blank slots with FILTER, and switch to column-major order with TRANSPOSE.
Turn multi-column rosters into a clean 1D list
Shift schedules and seating charts often spread names across several columns with empty gaps. TOCOL(array) flattens any 2D grid row-by-row into a single vertical column.
In this names-only example, remove both empty strings and zeros after flattening: FILTER(TOCOL(A2:C3),(TOCOL(A2:C3)<>"")*(TOCOL(A2:C3)<>0)). Excel converts blank source cells to zeros in default TOCOL, while Arte Sheets uses empty strings. Do not filter zeros from numeric datasets where zero is a valid observation.
| Desk1 (A) | Desk2 (B) | Desk3 (C) |
|---|---|---|
| Ava | Ben | Cara |
| Dan | (blank) | Eva |
Row-by-row vs column-by-column flattening
After removing blank/zero placeholders, row-major order is Ava, Ben, Cara, Dan, Eva. Applying TRANSPOSE before TOCOL gives column-major order: Ava, Dan, Ben, Cara, Eva.
=TEXTJOIN(", ", TRUE, FILTER(TOCOL(A2:C3), (TOCOL(A2:C3)<>"")*(TOCOL(A2:C3)<>0)))Compare populated count vs full grid slots
Filtering out empty/zero-coerced slots counts the 5 assigned staff, whereas ROWS(TOCOL(A2:C3)) returns all 6 grid positions.
=ROWS(FILTER(TOCOL(A2:C3), (TOCOL(A2:C3)<>"")*(TOCOL(A2:C3)<>0)))Try the example
Use the original values or change them to test your understanding.