Standardize IDs from Multiple Columns into One Column

Question

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:

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

Make sure to provide input and expected output as plain text table in the question. Check my answer or other options to create a table easily, which are easy to copy/paste. Avoid sharing links like spreadsheets, which make the question useless for others or images, which are hard to copy. Also, note that your email address can also be accessed by the public, if you share Google files. — TheMaster
– TheMaster, Commented Jan 12 at 13:45

z.. · Accepted Answer · 2025-01-12 08:54:06Z

2

You can use:

=ARRAYFORMULA(REDUCE(
  {"Product Name"\ "Tool Name"\ "ID Tool"\ "ID Tool Type"};
  SEQUENCE(COUNTA(A3:A));
  LAMBDA(a; i; LET(
    prod; INDEX(A3:A; i);
    tool; INDEX(B3:B; i);
    ids; INDEX(C3:E; i);
    VSTACK(
      a;
      IFERROR(
        SPLIT(
          TOCOL(
            prod & "❅" & tool & "❅" & TOROW(ids; 1) & "❅" & 
            REGEXEXTRACT(FILTER(C2:E2; ids <> ""); "\((.+)\)"));
          "❅";;
        );
        {INDEX(A3:B; i)\ ""\ ""}
      )
    )))))

edited Jan 12 at 8:54

answered Jan 12 at 7:52

z..

14k2 gold badges10 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Anna Jan 12 at 8:20

Sorry, I want to ask again, what if the null value in the ID Tool is in the middle of the row (not at the end of the row)? Does the formula still work? Anyway, I have updated the data in Google Sheets listed in the question with an additional 1 row below the null value in the ID Tool.

z.. Jan 12 at 8:51

I updated my formula

rockinfreakshow · Accepted Answer · 2025-01-12 08:09:11Z

1

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));Σ))))))

answered Jan 12 at 8:09

rockinfreakshow

30.7k4 gold badges19 silver badges30 bronze badges

2 Comments

Anna Jan 12 at 8:44

Sorry, I want to ask again, what if the null value in the ID Tool is in the middle of the row (not at the end of the row)? Does the formula still work? Anyway, I have updated the data in Google Sheets listed in the question with an additional 1 row below the null value in the ID Tool.

rockinfreakshow Jan 12 at 8:54

Yes... it will work

Harun24hr · Accepted Answer · 2025-01-12 07:39:45Z

1

Give a try to the following formula. See you file harun24hr sheet.

=VSTACK(QUERY(INDEX(SPLIT(UNIQUE(FLATTEN(INDEX(A3:A8&"|"&B3:B8&"|"&IF(C3:E8<>"";C3:E8&"|"&REGEXEXTRACT(C2:E2;"\((.+)\)");""))));"|"));"where Col3 is not null";0);QUERY(A3:D8;"Where C is null";0))

Output from above formula:

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

answered Jan 12 at 7:39

Harun24hr

38.1k6 gold badges24 silver badges47 bronze badges

1 Comment

Anna Jan 12 at 8:21

Sorry, I want to ask again, what if the null value in the ID Tool is in the middle of the row (not at the end of the row)? Does the formula still work? Anyway, I have updated the data in Google Sheets listed in the question with an additional 1 row below the null value in the ID Tool.

Collectives™ on Stack Overflow

Standardize IDs from Multiple Columns into One Column

3 Answers 3

2 Comments

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related