need help converting a single column of data to a table

I occasionally save a web page as html, then open it in order to get data.

So I save as html, open in Excel, and the result is a long, single column of data. However to be properly presented, I need to put the data into a 13 column table.

I tried using various OFFSET formulas to try to get it right, but I'm not succeeding. I've attached an example showing the raw data (in Column A) then the beginning of the desired table (beginning in cell E4).

(Note that Excel's Get Data from Web function often works on websites I use, but in this case it doesn't work. And the transpose feature of course becomes tedious when the column of data is hundreds or thousands of rows).

Thank You!

Put this formula in cell B27:

=IF(A26="",MAX(B$26:B26)+1,"")

then copy it down to the bottom of your data (i.e. B739 in your sample file). Then you can use this formula in cell F4:

=IF(ROWS($1:1)>MAX($B:$B),"",INDEX($A:$A,MATCH(ROWS($1:1),$B:$B,0)+COLUMNS($F:F)-1))

Copy this across to R4, and then you can copy the formulae in F4:R4 down until you start to get blanks.

Hope this helps.

Pete

Thanks Pete! A clever solution!

You're welcome - thanks for the rep.

Pete