function - How to convert a long form table to wide form table in Excel? -

a picture worth thousand words. let's in 1 sheet have following table:

using information, want programatically generate table this(sort of un-melting long table wide form) in sheet:

how can achieve this?

using vba:

range("g1:k99").clear each xx in range("a:a")     if xx.value = "" exit sub     range("g1").offset(xx.value, 0) = xx.value     e = 1 99         if range("g1").offset(xx.value, e) = ""             range("g1").offset(xx.value, e) = xx.offset(0, 1).value             exit         end if     next next 

the table it's created column "g". if want sheet:

sheets(2).range("g1: ... 

add sheets before ...

without vba, following scheme:

adding formulas:

m2 -> =iferror(match(l2;$a$1:$a$8;);"") n2 -> =iferror(match(l2;indirect("$a" & (m2+1) & ":$a$8");)+m2;"") o2 -> =iferror(match(l2;indirect("$a" & (n2+1) & ":$a$8");)+n2;"") p2 -> =iferror(index($b$1:$b$8;m2);"")          autocomplete columns r 

and autocomplete ...