Formula to unpivot data

You need a helper in c2, copied down:

=LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1

Then in G2, this array formula:
=IFERROR(INDEX($A$2:$A$5,MATCH(0,--(COUNTIF($G$1:G1,$A$2:$A$5)=$C$2:$C$5),0)),"")

Then in H2, this ordinary formula:
=IF(G2="","",--TRIM(MID(SUBSTITUTE(","&VLOOKUP(G2,$A$2:$B$5,2,FALSE),",",REPT(" ",125)),125*COUNTIF($G$2:G2,G2),125)))


Array Formulae are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

You're welcome....