google-sheetslambdagoogle-sheets-formulaspreadsheetarray-formulas

Standardize IDs from Multiple Columns into One Column

I have data related to Product Name, Tool Name, and ID Tool with the following data example:

Product Name Tool Name ID Tool (Standard) ID Tool (Alternative 1) ID Tool (Alternative 2)
Product A Tool A1 11-A1
Product A Tool A2 12-A2-01 12-A2-02 12-A2-03
Product A Tool A3 13-A3
Product B Tool B1 21-B1-01 21-B1-02
Product B Tool B2 22-B2
Product C Tool C1

I want to clean it up into the expected output as follows: enter image description here

For the output, I have tried the formula:

=LET(
    data; C3:E11;
    row; ROW(C3:E11)-ROW(C3:C11)+1;
    column; COLUMN(C3:E11)-COLUMN(C3:C11)+1;
    sorted_data; FLATTEN(data);
    label; FLATTEN(IF(COLUMN(C3:E11)-COLUMN(C3:C11)=0; "Standard"; "Alternative"));
    result; IFERROR(FILTER(HSTACK(sorted_data; label); LEN(sorted_data)>0); HSTACK(""; ""));
    result
)

but the tool type information doesn't appear and I'm still confused about how to enter the product and tool name because it keeps giving an error. Any suggestions for improving the formula?

Here's the test sheet link: https://docs.google.com/spreadsheets/d/11caWeL-PPyioovetrUf6rcy6iTb8vVuHgftAWzffpSM/edit?usp=sharing

Solution

  • You may try:

    =reduce(tocol(;1);A3:index(A:A;match(;0/(A:A<>"")));lambda(a;c;ifna(vstack(a;let(Λ;index(C:E;row(c));Σ;transpose(ifna(filter(vstack(Λ;regexextract(C2:E2;"\((.+)\)"));Λ<>"")));
        hstack(chooserows(index(A:B;row(c));sequence(rows(Σ);1;1;0));Σ))))))

    enter image description here