I need (and have had difficulty arriving at) a formulaic way to produce the values shown in columns D, E, and F.

CodePudding user response：

try:

=QUERY(A2:B; "select max(B) where B is not null group by B pivot A")

update 1:

=INDEX(TRIM(TRANSPOSE(SPLIT(FLATTEN(QUERY(QUERY(IF(A2:B="",,A2:B&"​"), 
 "select max(Col2) where Col2 is not null group by Col2 pivot Col1"),,9^9)), "​"))))

update 2:

={"School Supplies"; FILTER(B2:B, A2:A="School Supplies")}