arrange data in a specific excel table

[vikramv]

Hi..

This is vikram here. wanted help in arranging excel data in a particular way.
I have some data dealer code wise( A,B,C.D etc) for some parameters(B,D,R,C,E) for 4 months( Apr/May/Jun/July) in table 1. Would like to find out how the data in table 1 can be put in the format as given in table 2. Unable to do it using vlookup function.

please suggest.

Thank you in advance.

regards

vikram

 

[bosco_yip]

Try,

1] In table 2 A2, copied down :

=IFERROR(INDEX(Table1!A$3:A$11,INT((ROW(A1)-1)/5)+1),"")

2] In table 2 B2, copied down :

=IF(A2="","",INDEX(Table1!B$1:U$1,(MOD(ROW(A1)-1,5))*4+1))

3] In table 2 C2, copied across right and all copied down :

=IF($A2="","",INDEX(Table1!$B$3:$U$11,MATCH($A2,Table1!$A$3:$A$11,0),MATCH($B2,Table1!$B$1:$U$1,0)-1+MATCH(LEFT(C$1,3),Table1!$F$2:$I$2,0)))

Regards
Bosco