Google Sheets: Join data IF a condition is met

Question

I have data arranged as shown on column A of my sample sheet and I want to end up with an arrangement as shown on columns B and C. My real challenge, actually, is building a formula to get data as it is in Column C, as I already know how to do column B.

Any ideas? I must use formulas only, so no scripts...

Here is the sheet: https://docs.google.com/spreadsheets/d/1LEhXwZJsV4D9MTh45F9SjmbhW5-U8ceHHc5Qj9nw1Xk/edit#gid=0

Thanks in advance!

Answer 1

try:

=INDEX(REGEXREPLACE(TRIM(SPLIT(FLATTEN(QUERY(QUERY(SPLIT(
 IF(A2:A="",,SUBSTITUTE(A2:A, "+", "♠+")&","), "+"), 
 "select max(Col2) where Col2 is not null group by Col2 pivot Col1"),,
 9^9)), "♠")), " |,$", ))