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:
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?
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<>"")),))))