Google Sheets Formula to Clean An Array of Data

Question

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

Answer 1

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)))));)))))