sorting data from a PDF in Excel

Question

I have PDF with groups of data formatted in the style laid out below. My goal is to import this data into excel so that each line of data goes into a new column. So column A would just be Names of companies, B would be Licensed Numbers, etc. I have over 3k entries like this. Please let me know if you have any other questions or better yet if you have any solutions. :) Thanks in advance. APyz

"Name of Company"

Licensed # (i.e. "MC-1234")

        "CEO - [CEO'S NAME]"

        "Licensed [LICENSED DATE]"

                Address 1

                Address 2 (optional)

Answer 1

This can be done by using formulas or macro. I prefer macro. :-)

I assume that the PDF data is in column A and the data blocks are all equal in size.

Are they not, then you have to adjust the size. I can not that for you, unfortunately, you have not given enough information about your data structure.

Andreas.

Sub TransposeData()

  Dim FromR As Range, ToR As Range

  'First vertical data

  Set FromR = Range("A1:A6")

  'First cell for horizontal data

  Set ToR = Range("C1")

  Do

    'Copy vertikal data

    FromR.Copy

    'Transpose it to horizontal

    ToR.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

    'Setup Range to next data

    Set FromR = FromR.Offset(FromR.Rows.Count, 0)

    Set ToR = ToR.Offset(1, 0)

    'Loop until empty cell is found

  Loop Until IsEmpty(FromR(1, 1))

End Sub