Unpivot "Wide" data into a "Long" database format (Supports Multiple headers/columns)

Unpivot_Toolkit.lambda

/*
Name: UNPIVOT_PLUS	
Description: Given a table or a range with headers, Transforms "Wide" data into a "Long" database format.	
 V4 UPDATE: "pad_blanks_with" feature to replace empty grid cells (prevents Pivot Table type-errors).
 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_cols], [no_of_headers], [attribute_names], [value_name], [remove_errors], [remove_blanks], [pad_blanks_with], [pad_errors_with],
    LET(
        /* 1. Defaults: with shadowing pattern */
        no_of_headers, IF(ISOMITTED(no_of_headers), 1, no_of_headers),
        no_of_cols, IF(ISOMITTED(no_of_cols), 1, no_of_cols),
        attribute_names, IF(ISOMITTED(attribute_names), NA(), attribute_names), //Missing inputs default to na(): resolved later to 'Attribute.n' via IFERROR. 
        value_name, IF(ISOMITTED(value_name), "Value", value_name),
        remove_blanks, IF(ISOMITTED(remove_blanks), TRUE, remove_blanks), // This removes empty cells and strings.
        remove_errors, IF(ISOMITTED(remove_errors), FALSE, remove_errors), // Defaults to False to prevent silently ignoring errors.
        pad_blanks_with, IF(ISOMITTED(pad_blanks_with), "", pad_blanks_with), // Replaces both empty cells and empty strings.
        
        /* 2. Data Extraction */
        skip_rows, no_of_headers + (no_of_headers = 0),
        /* Case: no_of_headers=0. We skip Row 1 because it contains Anchor Titles, ensures the Grid starts at Row 2. */
        Grid, DROP(table, skip_rows, no_of_cols),
        Anchors, IF(no_of_cols > 0, DROP(TAKE(table, , no_of_cols), skip_rows), ""), // Handles zero anchors, guards against -ve values.
        raw_hdrs, IF(no_of_headers > 0, 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 & Grid Cleaning */
        r, SEQUENCE(ROWS(Grid)),
        c, SEQUENCE(, COLUMNS(Grid)),
        Grid_No_Blanks, IF(Grid = "", pad_blanks_with, Grid), // This catches empty cells and strings.
        //This skips IFERROR to leave original errors intact.
        Clean_grid, IF(ISOMITTED(pad_errors_with), Grid_No_Blanks, IFERROR(Grid_No_Blanks, pad_errors_with)),
        f_Grid, TOCOL(Clean_grid),
        
        /* 4. (Only if no_of_cols > 0) This loops through each anchor column: applies the SCAN (fill-down) logic then broadcasts it */
        expanded_anchors, IF(no_of_cols = 0,"",
            DROP(
                REDUCE("",SEQUENCE(no_of_cols),
                    LAMBDA(acc, anchor_idx,
                        LET(
                            anchor, INDEX(Anchors,, anchor_idx),
                            filled_col, SCAN("", anchor, LAMBDA(prev, curr, IF(curr = "", prev, curr))),
                            HSTACK(acc, TOCOL(IF(c, filled_col))) //broadcasting core logic
                        )
                    )
                ),,1
            )
        ),
        
        /* 5. This loops through each header row: applies the SCAN (fill-right) logic then broadcasts it + att. name handling*/
        expanded_hdrs_w_labels, IF(no_of_headers = 0,"",
            DROP(
                REDUCE("",SEQUENCE(no_of_headers),
                    LAMBDA(acc, hdr_idx,
                        LET(
                            hdr, INDEX(raw_hdrs, hdr_idx, 0),
                            filled_hdrs, SCAN("", hdr, LAMBDA(prev, curr, IF(curr = "", prev, curr))),
                            user_input, INDEX(attribute_names, hdr_idx), //process each Input
                            attr_label, IFERROR(user_input, "Attribute." & hdr_idx), // Default value
                            HSTACK(acc, VSTACK(attr_label, TOCOL(IF(r, filled_hdrs)))) //broadcasting core logic
                        )
                    )
                ),,1
            )
        ),
        
        /* 6. Smart Stack */
        header_and_grid, IF(no_of_headers = 0, VSTACK(value_name, f_Grid), HSTACK(expanded_hdrs_w_labels, VSTACK(value_name, f_Grid))),
        stacked, IF(no_of_cols = 0, header_and_grid, HSTACK(VSTACK(anchor_names, expanded_anchors), header_and_grid)),
        
        /* 7. handle all four states (Blanks, Errors, Both, None) */
        FILTER(stacked, 
            VSTACK(1,  //1 Forces header retention.
             NOT(
                 (remove_blanks * IFERROR(f_Grid="", FALSE)) // Safely traps empty cells,strings and errors.
                  +
                 (remove_errors * ISERROR(f_Grid))
                )
            )
        )
    )
);

/* 
Name: SPLIT_INJECT
Description: A slightly altered version of REPLACE_VECTOR. 
 Splits target columns by a delimiter and expands them horizontally. 
 If multiple indices are supplied, each target column is split independently.
 Non-target columns remain perfectly stable in their original positions.
 Optional "pad_with" replaces errors in the final result.
Made By: Medohh2120 
*/

SPLIT_INJECT = LAMBDA(array, target_indices, delimiter, [ignore_empty], [match_mode], [pad_with],
    REDUCE(array,SORT(TOCOL(target_indices), , -1),
        LAMBDA(arry, nxt, //starting from the right, the indices for the columns to the left remain perfectly stable.
            LET(
                Cols, COLUMNS(arry),
                Left, IF(nxt = 1, "", TAKE(arry, , nxt - 1)), //if split 1st column, there is no Left piece.
                Right, IF(nxt = Cols, "", DROP(arry, , nxt)), //if split last column, there is no right piece.
                ColToUnpack, INDEX(arry,, nxt),
                pack, TEXTSPLIT_COLUMN(ColToUnpack, delimiter,ignore_empty,match_mode,pad_with),
                IF(
                    nxt = 1,
                    HSTACK(pack, Right),
                    IF(nxt = Cols, HSTACK(Left, pack), HSTACK(Left, pack, Right))
                )
            )
        )
    )
);


/* 
Name: TEXTSPLIT_COLUMN
Description: Splits a 1D column of text by a delimiter and expands it horizontally.
 Optional "pad_with" replaces errors in the final result.
 Performance: High 
Made By: Medohh2120 
*/ 

TEXTSPLIT_COLUMN = LAMBDA(column, delimiter, [ignore_empty], [match_mode], [pad_with],
    LET(
        Width, MAX(MAP(column, LAMBDA(r, COLUMNS(TEXTSPLIT(r, delimiter, , ignore_empty, match_mode))))),
        /* If omitted, Excel treats pad_with as 0. ISOMITTED forces it to an error. */
        safe_pad, IF(ISOMITTED(pad_with), #N/A, pad_with),
        result, DROP(
            REDUCE("",SEQUENCE(Width),
                LAMBDA(acc, word_idx,
                    LET(
                        /* Create a single column for this word index */
                        NewCol, MAP(
                            column,
                            LAMBDA(cell,
                                INDEX(TEXTSPLIT(cell, delimiter, , ignore_empty, match_mode), word_idx)
                            )
                        ),
                        HSTACK(acc, NewCol)
                    )
                )
            ),,1
        ),
        IFERROR(result,safe_pad)
    )
);

You can find File with use cases Here