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:
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
