Help a novice format vast amounts of data.. please!

[majandra22]

Hello all,

For my work I am trying to take excel data from an experiment and format it for use in a different program. I think what I need to do is straightforward enough, but I don't know the functions or have the excel terminology to find the solution online. I'd really appreciate it if you can suggest some simple formulaic solutions (no VBA codes or outside apps) so that I don't have to reformat the document piece by piece.


I recreated a simple model to show what I'm trying to achieve. B3:B5 are three pieces of data related to T1, so they need to be transposed into cells E3, F3, and G3. Then C3:C5 would transpose to H3:J3, and D3:D5 to K3:M3. This would condense the information from three separate rows into one. The same would then happen to B6:B8, C6:C8, D6:D8 and so on down the rows. Overall there are about 1400 rows of data that need to be reformatted into 33 columns, so any help that keeps me from doing it bit by bit would be great.

current.png
Current set-up

desired result.png
Desired format

I'm thinking I will have to use transpose and arrays, and hopefully someone knows a formula that I can extend across the 33 columns, then copy and paste into other rows and have excel recognize the pattern to hopefully be able to drag/extend it all the way down through the 1400. Any suggestions are appreciated!

[scottiex]

You could just create a pivot table.
maybe add a column to the left of the 100 one that says something like

Please Login or Register  to view this content.

and use that as a column header to make it easy

[azumi]

Consider your table is on Sheet1 from ranges A3 to M2, put this on Sheet2 on cell A2 and copied down :
=IFERROR(INDEX(Sheet1!$A$3:$A$14,MATCH(0,INDEX(COUNTIF($A$1:A1,Sheet1!$A$3:$A$14&""),0,0),0)),"")

And put this on Sheet2 cell B2 and copied down and cross :
=IF(AND(COLUMNS($A$1:A1)>=1,COLUMNS($A$1:A1)<=3),IFERROR(INDEX(Sheet1!$B$3:$B$14,(ROWS(A$1:A1)-1)*3+COLUMNS($A$1:A1)),""),IF(AND(COLUMNS($A$1:A1)>=4,COLUMNS($A$1:A1)<=6),IFERROR(INDEX(Sheet1!$C$3:$C$14,(ROWS(A$1:A1)-1)*3+COLUMNS($A$1:A1)-3),""),IF(AND(COLUMNS($A$1:A1)>=7,COLUMNS($A$1:A1)<=9),IFERROR(INDEX(Sheet1!$D$3:$D$14,(ROWS(A$1:A1)-1)*3+COLUMNS($A$1:A1)-6),""))))