Assuming that Column A of Sheet 1 contains your data, and each record is
made up of six rows, such as...
Jack
Jack1
Jack2
Jack3
Jack4
Jack5
John
John1
John2
John3
John4
John5
....and you want to transpose the data as follows...
Jack Jack1 Jack2 Jack3 Jack4 Jack5
John John1 John2 John3 John4 John5
....on your other sheet, enter the following formula, copy across five
columns and down until you get zeros:
=OFFSET(Sheet1!$A$1,ROW(1:1)*6-6+COLUMN(B:B)-COLUMN($B$1),0)
Hope this helps!