Forum Discussion
unpivot data and handle merged cells without using Power Query (Unpivot_Toolkit)
Hey, guys!!
I’ve been working on a set of functional Excel Lambdas to solve a common headache: transforming "Wide" human-readable data into "Long" database formats without having to open Power Query every time.
=UNPIVOT_PLUS(table, [no_of_cols], [no_of_headers], [attribute_names], [value_name], [remove_errors], [remove_blanks],[pad_blanks_with],[pad_errors_with])Don’t worry about the full list, most use cases only require 5 arguments. I've included a table of default values at the end.
Merged Cell Support: Automatically handles fill-down/fill-right logic for merged headers/columns.
Bonus Helper: SPLIT_INJECT
=SPLIT_INJECT(array, target_indices, delimiter, [ignore_empty], [match_mode], [pad_with])It targets specific columns, splits them by a delimiter (like TEXTSPLIT), and expands the entire table horizontally while keeping all other columns perfectly stable. Optional arguments match TEXTSPLIT defaults.
Feel free to tear this apart or adapt it for your own edge cases. I’d love to hear how you end up using it!
You can grab both functions from my GitHub Gist https://gist.github.com/Medohh2120/f8553c149684e39bb499249e39f01017
File with use cases https://1drv.ms/x/c/6c310c8fd1669a94/IQCscpo3yh7jR5XdGZe2AQGyAf4-vCd8K6BvLZQgrP2V8Oo?e=gPdbOd
| Argument | Description | Default Behavior |
|---|---|---|
| table | The array or range of data to unpivot. | Required argument (no default) |
| [no_of_cols] | Fixed left columns to keep as identifiers. | 1 |
| [no_of_headers] | Top rows used as headers, handling merged cells. | 1 |
| [attribute_names] | Header name for the unpivoted attributes . | "Attribute" |
| [value_name] | Header name for the unpivoted values. | "Value" |
| [remove_errors] | Excludes grid rows with formula errors. | FALSE |
| [remove_blanks] | Removes grid empty cells and empty strings. | TRUE |
| [pad_blanks_with] | Value to substitute for empty cells. | Leaves cell blank |
| [pad_errors_with] | Value to substitute for errors. | Leaves error as-is |
5 Replies
Looks impressive!
A useful step towards providing new calculation options.
- My first choice might be to use array formulas directly on the data objects held within your crosstab
- The next might be to use your 'souped up' unpivot followed by database-style aggregations
- Repivot to form a new crosstab with the restructured arrays for calculation
Choice is good.
Thanks, Peter! Really appreciate the kind words and the thoughtful breakdown.
I'll be honest. Your three points are a little open-ended, so I may not be reading them exactly as intended! But if I'm on the right track, here's how they map to some design decisions I made along the way:
On option 1. it's definitely the most intuitive approach, and honestly the most Excel-native one. The catch I ran into is performance. Once fill-down/fill-right logic is a part of the formula chain, recalculation overhead grows noticeably compared to working against a plain range reference. I benchmarked this across a few scenarios using a custom LAMBDA benchmark tool, and the difference was significant (x2 slower).
I even update the function to use INDEX instead of CHOOSEROWS/COLS to return references not arrays and got 2 sec boost in worst case scenarios. I'm not entirely sure why, but the numbers don't lie.
On options 2 & 3. if you're pointing toward aggregation and re-pivoting as natural next steps after unpivoting, then totally agree. I am happy to make a separate version of PIVOT/GROUPBY that integrates perfectly with UNPIVOT_PLUS without DROP/TAKE Headache soon. UNPIVOT_PLUS is already sitting at 9 parameters (PIVOTBY is at 11), which I feel is the upper limit before users start bouncing off it (though I know that's no issue for power users).
Integrating both into one will be a whopping 20-parameter machine!
If I've misread any of this, feel free to correct me. Always happy to dig deeper! And yes, choice is indeed good.
Can you please share a sample file with few of a examples?
Of course! I've added a sample file in the post above.
Note: there seems to be a bug with hyperlinks on this platform, the links appear correctly in the editor but don't render as clickable in the published post. You can still access the file by copy-pasting the link directly into your browser.
This looks really nice. I haven't dug in but just wanted to thank you for sharing your hard work with us.
