UNPIVOT (My Custom Excel Function)

"Thanks LAMBDA"

Please welcome my new Custom Function UNPIVOT
This FUNCTION empowers you to efficiently Flat your data, similar to
Power Query's unpivot functionality, but with enhanced flexibility and control

= UNPIVOT ( Range , Cols , Blank Options , Header )

It consists of 4 parts

Range : Select the range of data you want to unpivot.

Cols:
Specify which columns to treat as "keys" (preserved after unpivoting) and which to unpivot (spread into rows).

** Default: Use the first leftmost column as the key and unpivot all other columns. (Same behavior for entering "1") or you can skip it.

** Positive Integers: Specify the number of leftmost columns to be keys and unpivot the remaining columns. (e.g., "2" uses the first two columns as keys and unpivot other Cols.)

** Negative Integers: Specify the number of rightmost columns to be keys and unpivot the remaining columns. (e.g., "-1" uses the last column as the key and unpivot other Cols.)

Blank Options:

Choose how to handle empty cells during unpivoting:
1 : Include only rows with empty cells.
2 : Include only rows with non-empty cells.
3 : Include all rows (empty and non-empty).

Headers:

Default (Blank) "" or skip : No header will be added to the unpivoted data.
Custom Header:

Specify a header within curly braces: {"Name","Loc",Qty."} Or you can use HSTACK .. And for Numeric Sequence you can use SEQUENCE Function to Generate sequential headers (1, 2, 3...).