Dynamically Unpivot without Power Query with Merge Columns and Rows

[dluhut]

Hello Excel Gurus,

Without using Power Query, how do you unpivot columns and have the result to be in dynamic array with an additional "break"?

PS: I'm only looking for a formula. Not VBA or Power Query.

For example, the raw data is given from cell A1 to F5. I'd like to have it return in a dynamic array shown in cell J1 to N15. Yes including "custom" header

The custom header is 'Product', 'Count Type' and 'Actual'. <-- I can accept it to be a static header if it's not doable.

Notice too that when it's to a new "week", there's an empty row that act as a separator.

Thank you in advanced.

Dynamically Unpivot without Power Query including Custom Headers.png

[John Vergara]

Hi dluhut!

Check this formula:

=LET(r,A3:B5,h,C1:F1,c,COLUMN(h),v,C3:F5,DROP(REDUCE(HSTACK(A1:B1,{"Product","Count Type","Actual"}),SEQUENCE(ROWS(r)),LAMBDA(i,x,VSTACK(i,HSTACK(INDEX(r,IFNA(EXPAND(x,COLUMNS(v)),x),SEQUENCE(,COLUMNS(r))),TRANSPOSE(VSTACK(LOOKUP(c,c/(h>""),h),C2:F2,INDEX(v,x,)))),T(+SEQUENCE(,5))))),-1))  

File attached. Blessings!

[dluhut]

Solved and reps up to you @John Vergara!

I modified your formula a little to have the formula take the last column and last row. So that if new data is added, it'll automatically populate