google-sheetsgoogle-sheets-formulaspreadsheet

Google Sheets Formula to Clean An Array of Data

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

Product Code Product Name Item Name
01 A001 Primary -
Primary Book
Primary Fruit
Primary -
01 A002 Secondary Pen
Secondary -
Secondary Cloth
02 A003 Secondary -
Second -
Second Pencil
02 A004 - -
- -

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(Λ;tocol(;1); reduce(Λ;unique(tocol(C4:C18 ;1));lambda(a;c;ifna(vstack(if(iserr(+a);Λ;a);let(Σ;filter(D4:D18 ;scan(;C4:C18;lambda(f;q;if(q="";f;q)))=c;D4:D18<>"-";D4:D18<>"");if(isna(Σ);Λ;vstack(hstack(xlookup(c;C4:C18;A4:B18);c;Σ);))))))))

but a product name that has 2 values outputs the last line. The output should be the first line. Any suggestions for improving the formula?

Here's the test sheet link: https://docs.google.com/spreadsheets/d/1PoksiSGa9lEjhVPugiJhZd2vCw3R5SGZUw7-X2bNazY/edit?usp=sharing

Solution

  • You may try:

    =let(Σ;filter(A4:C18;A4:A18<>"";C4:C18<>"-"); Ξ;lambda(x;scan(;x;lambda(a;c;if(c="";a;c))));
     reduce(tocol(;1);sequence(rows(Σ));lambda(a;c;ifna(vstack(a;let(x;chooserows(Σ;c);d_;D4:D18;hstack(x;filter(d_;d_<>"-";d_<>"";Ξ(A4:A18)&Ξ(B4:B18)=join(;choosecols(x;1;2)))));)))))

    enter image description here