UNPIVOT (My Custom Excel Function)

"Thanks LAMBDA" 

Please welcome my new Custom Function https://www.linkedin.com/feed/hashtag/?keywords=unpivot&highlightedUpdateUrns=urn%3Ali%3Aactivity%3A7158368034926010368
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...).

Example 1 :

Basic unpivot, ( wait till Example 3 to know the meaning of " 3 " in Blank Options)

Example 2 :

Unpivot data and add headers

Example 3 :

let's show the Blank Options and how it works

 Example 4 :

unpivot based on which cols

WHAT DO YOU THINK ? IS IT USEFUL ? WOULD IT SAVE TIME?

If you agree with this, I encourage you to vote for my suggestion

https://feedbackportal.microsoft.com/feedback/idea/e6aea412-0fc0-ee11-92bd-6045bd7fe601

https://www.linkedin.com/feed/hashtag/?keywords=hazem_hassan&highlightedUpdateUrns=urn%3Ali%3Aactivity%3A7158368034926010368
https://www.linkedin.com/feed/hashtag/?keywords=dr_excel&highlightedUpdateUrns=urn%3Ali%3Aactivity%3A7158368034926010368
https://www.linkedin.com/feed/hashtag/?keywords=excel&highlightedUpdateUrns=urn%3Ali%3Aactivity%3A7158368034926010368 https://www.linkedin.com/feed/hashtag/?keywords=excelskills&highlightedUpdateUrns=urn%3Ali%3Aactivity%3A7158368034926010368 https://www.linkedin.com/feed/hashtag/?keywords=exceltricks&highlightedUpdateUrns=urn%3Ali%3Aactivity%3A7158368034926010368 https://www.linkedin.com/feed/hashtag/?keywords=exceltips&highlightedUpdateUrns=urn%3Ali%3Aactivity%3A7158368034926010368 https://www.linkedin.com/feed/hashtag/?keywords=mvp&highlightedUpdateUrns=urn%3Ali%3Aactivity%3A7158368034926010368 https://www.linkedin.com/feed/hashtag/?keywords=mvpdevelopment&highlightedUpdateUrns=urn%3Ali%3Aactivity%3A7158368034926010368 https://www.linkedin.com/feed/hashtag/?keywords=mvpbuzz&highlightedUpdateUrns=urn%3Ali%3Aactivity%3A7158368034926010368 https://www.linkedin.com/feed/hashtag/?keywords=mvps&highlightedUpdateUrns=urn%3Ali%3Aactivity%3A7158368034926010368 https://www.linkedin.com/feed/hashtag/?keywords=microsoftambassador&highlightedUpdateUrns=urn%3Ali%3Aactivity%3A7158368034926010368