I want to normalize following table in Google Sheet:

Process	Tool A	Tool B
Process 1	Tool 1	Tool 2
Process 2	Tool 1
Process 3	Tool 2	Tool 3

I want to normalize following table in Google Sheet:

Process	Tool A	Tool B
Process 1	Tool 1	Tool 2
Process 2	Tool 1
Process 3	Tool 2	Tool 3

into the following 1NF table:

Process	Tool A	Tool B
Process 1	Tool 1
Process 1	Tool 2
Process 2	Tool 1
Process 3	Tool 2
Process 3	Tool 3

There are various options (Google Sheets QUERY() function, etc.).

• sql
• google-sheets
• first-normal-form

Share Improve this question Follow edited Feb 4 at 12:19 philipxy 15.2k66 gold badges4343 silver badges9797 bronze badges asked Feb 2 at 20:27 SaxSax 1122 bronze badges

Add a comment  | 

3 Answers 3

Sorted by: Reset to default 1

Use wraprows(), like this:

=let( 
  get_, lambda(a, filter(hstack(A2:A, a), len(a))), 
  wraprows(tocol(hstack(get_(B2:B), get_(C2:C)), 3), 2) 
)

See let(), lambda(), hstack(), filter(), wraprows(), and tocol().

Try this Alternative approach

=QUERY(ARRAYFORMULA(SPLIT(FILTER(FLATTEN(A2:A&"|"&B2:C), FLATTEN(B2:C)<>""), "|")),"SELECT Col1, Col2 LABEL Col1 'Process', Col2 'Tool A'",0)

Expected Output

Process	Tool A
Process 1	Tool 1
Process 1	Tool 2
Process 2	Tool 1
Process 3	Tool 2
Process 3	Tool 3

References

• QUERY()

• FLATTEN()

• SPLIT()

You may try:

=reduce(A1:B1,B2:C,lambda(a,c,vstack(a,if(c="",tocol(,1),{index(A:A,row(c)),c}))))