google-sheetsgoogle-sheets-formulaspreadsheet

Google Sheets formula to move and clean an array of data


I have data related to product code and item name with the following data example:

Product Code Item Name
A001 -
Book
Fruit
-
A002 Pen
-
Cloth
A003 -
-
Pencil

I want to clean it up into expected output as follows: Expected Output

For the output, I have tried with formula:

=QUERY(ARRAYFORMULA({TRIM(Dummy!B2:B), TRIM(Dummy!C2:C)}), "SELECT * WHERE Col2 <> '-'")

but the product code with the same row as the item name “-” is also deleted, is there any other formula to show the product code?


Solution

  • Here's one possible approach which you may adapt accordingly:

    =reduce(tocol(,1),unique(tocol(B2:B,1)),lambda(a,c,ifna(vstack(a,hstack(c,filter(C:C,scan(,B:B,lambda(f,q,if(q="",f,q)))=c,C:C<>"-",C:C<>"")),))))
    

    enter image description here