Finding duplicate values in a col and copying values from adjacent cell with specific characters to another cell

Question

I have an excel spreadsheet with the following data

Data

and I am trying to change it into this format

Desired output

I tried the following

=IFERROR(INDEX($B:$B,MATCH($A18,$A:$A,0)),0)

but it only returns the first product name that match the code

Data 2

I added a search to the previous but that did not work for green.

=IF(ISNUMBER(SEARCH("green",IFERROR(INDEX($B:$B,MATCH($A2,$A:$A,0)),0))),IFERROR(INDEX($B:$B,MATCH($A2,$A:$A,0)),0),"")

Could someone please help?

Answer 1

In one go:

Formula in D2:

=LET(X,A2:A7,IFERROR(UNIQUE(HSTACK(A2:A7,MAKEARRAY(ROWS(X),MAX(COUNTIF(A2:A7,A2:A7)),LAMBDA(r,c,INDEX(FILTER(B2:B7,X=INDEX(X,r)),c))))),""))

Probably overthinking this...