How do I reorder cells in Excel?

Question

I recently had to gather some data from a website for which I used a simple scraping script to collect and store the data in an excel file. The problem I'm facing now is that in the present form, I can hardly use the data for any purpose. Right now, my excel file looks something like this:

I need it to be in a more 'readable' and concise format such as this

I obviously have to transpose the smaller columns and arrange them one on top of the other but I have no clue how to go about doing that.
Are there any methods to automate this process?

Answer 1

In column I write 1 in I2, 2 in I3 select 1 and 2 and drag down till the last number you have.
In J2 write:
=IFERROR(INDEX($E$2:$E$24,MATCH($I2,$E$2:$E$24,0)+1),"")
In K2 write:
=IFERROR(INDEX($E$2:$E$24,MATCH($I2,$E$2:$E$24,0)+2),"")

Change E2:E24 till the last row in your Data and keep $ to fix the references while dragging the formula
Drag both formulas down in the same column
It will return the Data (column I, J, K)if any or empty "" if no values
When finish you can select the new Data Copy, Paste special Values in another place and you can use it normally

Answer 2

If the structure of the data is regular as in the example, you just need to convert an index in a linear array into row and column indices of the matrix and use INDEX() to populate it.

Type this formula

=IFERROR(INDEX($E$2:$E$24,(ROW()-2)*4+COLUMN(A1)),"")

into I2 and drag/copy it down.

Answer 3

Here is a formula that should work for you (in i2):

=INDEX($E$1:$C$23, SMALL(INDEX((I$1=$D$1:$D$24)*(MATCH(ROW($D$1:$D$24), ROW($D$1:$D$24)))+(I$1<>$D$1:$D$24)*1048577, 0, 0), ROW(Z1)))