Stop using ungodly INDEX math to flatten 2D schedules. TOCOL() + FILTER() is all you need.

This comes up constantly. Someone gets handed a resource tracker or a system export where tasks are split across "Morning Task" and "Afternoon Task" columns, and they need a flat list to dump into a Pivot Table. Simple enough ask.

The fun part? Half these exports don't even leave cells blank - they write out [empty] as literal text, so any trick that relies on detecting blank cells just falls flat. And on top of that there's usually a Status column you need to drag along, but only once per person - not stamped next to every single task row like a broken rubber stamp.

Old solution was some deeply cursed nested INDEX/ROW formula that nobody could read six months later. If you're still doing that, please stop.

On Office 365 you can handle the whole thing in one shot:

=LET(data, A2:C11, status, D2:D11, col_data, TOCOL(data), col_status, TOCOL(IF(SEQUENCE(1,COLUMNS(data))=1, status, "")), FILTER(HSTACK(col_data, col_status), col_data<>"[empty]"))

TOCOL flattens the grid, the IF/SEQUENCE combo makes sure the status only shows up next to the name and not repeated under every task, HSTACK glues the two columns together, and FILTER kills all the [empty] noise. Keep your ranges the same size throughout or you'll get a #VALUE! staring back at you.

These dynamic array functions honestly flew under the radar for a lot of people who don't spend their weekends reading Excel update logs. Hope it saves someone a headache.

Note; for Excel 2019 and 2021 you can use power query. However in the 2021 version you can use the filter and sequence function.

In 2021 version; the formulas are;

=FILTER(INDEX(A2:C11,MOD(SEQUENCE(30)-1,10)+1,INT((SEQUENCE(30)-1)/10)+1),INDEX(A2:C11,MOD(SEQUENCE(30)-1,10)+1,INT((SEQUENCE(30)-1)/10)+1)<>"[empty]")

and

=FILTER(IF(INT((SEQUENCE(30)-1)/10)+1=1,INDEX(D2:D11,MOD(SEQUENCE(30)-1,10)+1),""),INDEX(A2:C11,MOD(SEQUENCE(30)-1,10)+1,INT((SEQUENCE(30)-1)/10)+1)<>"[empty]")

one thank go at user for pointing it out to me Excel_User_1977