Thread: Macro to convert 1 column to a 13 column x N row matrix

I pasted a data table from a pdf into excel, and now I'm trying to recreate the table as it looked in the pdf. The pdf was in landscape mode, and unfortunately I couldn't find a pdf-to-excel conversion software that worked for this type of pdf. IF there was a good pdf-to-excel converter that would solve the problem. Any suggestions?

IF there aren't:

As it stands, I have a single column of data with ~600,000 rows and now trying to parses it into matrix of N rows by 13 columns.

This would be a simple matter if the data were clean, but the data isn't cleanly divisible by 13, and correcting these data gaps would take a lot of time.

Can I overcome this problem by selecting what the first value of each row in the matrix starts with? I have very limited experience writing macros (just started learning today), so I’d love to hear what more experienced people think. If my post is unclear, I am happy to clarify any of the details.

Easiest if you attach a workbook with a sample of data as is, and desired results.

Originally Posted by StephenR

Easiest if you attach a workbook with a sample of data as is, and desired results.

See attached file. thanks.

desired results

Please can you add this bit - not sure what you want doing?

Originally Posted by StephenR

Please can you add this bit - not sure what you want doing?

sure. what I want is to take my single column of data and make a 13 column x n matrix; taking the first 13 entries and making them the first row, the next 13 entries as the 2nd row, and so forth. I did this in excel with a function that basically sorts every 13 cells into a row, but that gets thrown off because there are sometimes more or less than 13 entries of data (ie. it isn't divisible by 13).

This led me to believe that I was in macro-land, where I am not experienced enough to write the code. does this explain things?

Like so? Puts results on a second sheet. Still not entirely clear as your last post refers to rows. I've assumed you meant columns.

Code:

Sub x()

Dim rng As Range

Set rng = Sheet1.Range("A1").Resize(13)
Do While Not IsEmpty(rng(1, 1))
    rng.Copy Sheet2.Cells(1, Columns.Count).End(xlToLeft).Offset(, 1)
    Set rng = rng.Offset(13)
Loop

End Sub

Originally Posted by StephenR

Like so? Puts results on a second sheet. Still not entirely clear as your last post refers to rows. I've assumed you meant columns.

Code:

Sub x()

Dim rng As Range

Set rng = Sheet1.Range("A1").Resize(13)
Do While Not IsEmpty(rng(1, 1))
    rng.Copy Sheet2.Cells(1, Columns.Count).End(xlToLeft).Offset(, 1)
    Set rng = rng.Offset(13)
Loop

End Sub

thanks for your ideas in thinking about this problem! I think i figured it out, with the help of a friend.

[no more follow-up posts required]