Change data format

[sms2luv]

Team,

I am updating image here, need your assistance.

Above data is the format which I receive and I want to convert it into the format which is at this bottom.

 

[David Evans]

Hard to tell if this will work, as your picture makes it hard to test anything .... anyway take a look at this ...

Or - upload a sample excel file - screenshots won't get you a definitive answer, unfortunately

 

[bosco_yip]

Try,

1] B10 copy across to D10 and all copy down :

=IFERROR(INDEX(A$2:A$6,INT((ROWS(A$1:A1)-1)/(MATCH(9^9,$1:$1)-3))+1),"")

2] E10, copy down :

=IF(B10="","",OFFSET(C$1,,MOD(ROWS(A$1:A1)-1,MATCH(9^9,$1:$1)-3)+1))

3] F10, copy down :

=IF(B10="","",OFFSET(C$1,MATCH(B10,A$2:A$6,0),MOD(ROWS(A$1:A1)-1,MATCH(9^9,$1:$1)-3)+1))

Regards
Bosco