Snasui Excel Forum

Hi Dear

I got some problem to re arrange data for pivot table but my data is not suitable for pivot.i google power query can do but I don't know power query hence I ask here for solution with formula.

I attached a sample for your reference
Thanks

C10=IFERROR(INDEX(INDEX($A$3:$D$6,0,MATCH(TRUE,SUBTOTAL(3,OFFSET($A$3:$A$6,,,,COLUMN($A$2:$D$2)-COLUMN($A$2)+1))>=ROWS(C$10:C10),0)),AGGREGATE(15,6,(ROW($A$3:$A$6)-ROW($A$3)+1)/(INDEX($A$3:$D$6,0,MATCH(TRUE,SUBTOTAL(3,OFFSET($A$3:$A$6,,,,COLUMN($A$2:$D$2)-COLUMN($A$2)+1))>=ROWS(C$10:C10),0))<>""),IFERROR(ROWS(C$10:C10)+1-LOOKUP(ROWS(C$10:C10),SUBTOTAL(3,OFFSET($A$3:$A$6,,,,COLUMN($A$2:$D$2)-COLUMN($A$2)+1))+1),ROWS(C$10:C10)))),"")

B10=INDEX(A$2:D$2,AGGREGATE(15,6,(COLUMN(A$3:D$3)-COLUMN(A$3)+1)/(A$3:D$6=C10),COUNTIF(C$10:C10,C10)))
All Copy down

Thank you in B10 why so complex,is there any workaround besides this?