Last active
February 4, 2026 22:36
-
-
Save Medohh2120/f8553c149684e39bb499249e39f01017 to your computer and use it in GitHub Desktop.
Wide" data into a "Long" database format
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /* | |
| Name: UNPIVOT_PLUS | |
| Description: Given a Table or a range with headers, Transforms "Wide" data into a "Long" database format. | |
| V3 UPDATE: Now supports multi-row headers and multi-column anchors. | |
| Automatically handles merged cells (Fill-Down or Fill-Right logic). | |
| optionally removing Grid blank entries. | |
| optionally removing Grid errors. | |
| Made By: Medohh2120 | |
| */ | |
| UNPIVOT_PLUS = LAMBDA(Table, [No_of_headers], [No_of_cols], [Col_names], [Value_name], [Remove_blanks], [Remove_errors], | |
| LET( | |
| /* 1. Defaults */ | |
| No_of_headers, IF(ISOMITTED(No_of_headers), 1, No_of_headers), | |
| No_of_cols, IF(ISOMITTED(No_of_cols), 1, No_of_cols), | |
| Col_names, IF(ISOMITTED(Col_names), "", Col_names), | |
| value_name, IF(ISOMITTED(Value_name), "Value", Value_name), | |
| Remove_blanks, IF(ISOMITTED(Remove_blanks), TRUE, Remove_blanks), | |
| Remove_errors, IF(ISOMITTED(Remove_errors), TRUE, Remove_errors), | |
| /* 2. Data Extraction */ | |
| Grid, DROP(Table, No_of_headers, No_of_cols), | |
| Anchors, IF(No_of_cols > 0, DROP(TAKE(Table, , No_of_cols), No_of_headers), ""), | |
| raw_hdrs, TAKE(DROP(Table, , No_of_cols), No_of_headers), | |
| anchor_names, IF(No_of_cols > 0, INDEX(Table, No_of_headers, SEQUENCE(, No_of_cols)), ""), | |
| /* 3. Helper Values */ | |
| r, ROW(Grid), | |
| c, COLUMN(Grid), | |
| f_Grid, TOCOL(Grid), | |
| /* 4. Anchor Processing (Only if No_of_cols > 0) */ | |
| f_anchors, IF(No_of_cols = 0,"", | |
| DROP( | |
| REDUCE("",SEQUENCE(No_of_cols), | |
| LAMBDA(acc, n, LET( | |
| col_data, INDEX(Anchors, , n), | |
| filled_col, SCAN("", col_data, LAMBDA(a, b, IF(b = "", a, b))), | |
| HSTACK(acc, TOCOL(IF(c, filled_col)))) | |
| ) | |
| ),,1 | |
| ) | |
| ), | |
| /* 5. Header Processing his loops through each anchor column, applies the SCAN (fill-down) logic then broadcasts each (Always runs) */ | |
| header_stack, DROP( | |
| REDUCE("",SEQUENCE(No_of_headers), | |
| LAMBDA(acc, nxt, | |
| LET( | |
| user_input, IFERROR(INDEX(TOCOL(Col_names), nxt), ""), | |
| filled_hdrs, SCAN("", INDEX(raw_hdrs, nxt, ), LAMBDA(a, b, IF(b = "", a, b))), | |
| HSTACK(acc, VSTACK(IF(user_input <> "", user_input, "Col" & nxt), TOCOL(IF(r, filled_hdrs)))) | |
| ) | |
| ) | |
| ),,1 | |
| ), | |
| /* 6. Smart Stack */ | |
| header_and_grid, HSTACK(header_stack, VSTACK(value_name, f_Grid)), | |
| stacked, IF(No_of_cols = 0, header_and_grid, HSTACK(VSTACK(anchor_names, f_anchors), header_and_grid)), | |
| /* 7. Final Filter */ | |
| FILTER(stacked, | |
| VSTACK(1, | |
| NOT( | |
| (Remove_blanks * ISBLANK(f_Grid)) | |
| + | |
| (Remove_errors * ISERROR(f_Grid)) | |
| ) | |
| ) | |
| ) | |
| ) | |
| ); |
Author
Author
v3 UPDATE: Syntax has changed with multiple added features, please check function Description
Author
0 anchor columns case : (in case of 0 header, transpose the whole data and use this case)
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Warning: Data range Must be updated according to given number of headers:
2 Headers in action:
1 Header in action (defaults to 1 header):
[NEW] N headers (you can add as much as you want)
Note:
column_namesis realted tonumber_of_headersNOT primary left most column, and It's put in vertical or horizontal array/reference according to your likings as in:{header1;header2}vstack(header1,header2)a3:a5