Importing or Changing the data from a FORMATTED Excel Worksheet into Tabular Form

Hi,
I have large sets of data from an external system that is preformatted for printing. Unfortunately this means all relevant data is not in a tabular format that I can sort and run additional analysis on. I have attached a file with sample data, but I am looking for a way to pull data from a worksheet formatted like:

Name 1, Dept
XX-XXXX1
Address 1
City, State Zip Code USE

Posting Date Transaction Date Description Receipt Amount Posted Amount Expense Amount
07/9/2014 07/09/2014 Vendor 1 2,039.94 2,039.94 2,039.94

Expense Description: Pink Bunnies

Accounting Codes
Inventory Transaction?: No Part Number: N/A Work Order #: n/a
Operating Unit: 02 Department: 66666 GL Account: 12345678
Fund Code: OPR Function: 041 Project: Project Not Required
Activity: Activity Not Required

In to a format like:

Name Card# Address 1 Address 2 Posting Date Transaction Date Description Receipt Amount Posted Amount Expense Amount Expense Description
Vendor 1 X-XXX1 Address 1 City, State, Zip Code 07/09/2014 07/09/2014 Vendor 1 2,039.94 2,039.94 2,039.94 Pink Bunnies

Thanks in advance for any assistance,
Zinnia

Hi !

If data are imported, faster is to directly import source file to desired format, more efficient …

From a worksheet, time execution increases …

Unfortunately, I am a few levels removed from the importing the data. The person with direct access to the system can only give me the data in this format.

PHP Code: 

Sub Demo()
    Dim VA(9 To 22)
    L& = 2:  R& = 9
    Sheet2.UsedRange.Offset(2).Clear
    Application.ScreenUpdating = False

    With Sheet1
            M& = .UsedRange.Rows.Count
        Do
            VT = Application.Transpose(.Cells(R, 1).Resize(4).Value)
             R = R + 4
                        Do Until IsDate(.Cells(R, 1).Value)
                            R = R + 1:  If R = M Then Exit Sub
                        Loop
        Do
               L = L + 1:  Sheet2.Cells(L, 1).Resize(, 4).Value = VT
        .Cells(R, 1).Resize(, 4).Copy:  Sheet2.Cells(L, 5).PasteSpecial xlPasteValuesAndNumberFormats
                    Sheet2.Cells(L, 9).Resize(, 3).NumberFormat = "#,##0.00"
                  Sheet2.Cells(L, 11).Resize(, 12).NumberFormat = "@"
         VA(9) = .Cells(R, 6).Value:                     VA(10) = .Cells(R, 8).Value
        VA(11) = Split(.Cells(R + 1, 1).Value, ":")(1):  VA(12) = .Cells(R + 2, 2).Value
        VA(13) = .Cells(R + 3, 2).Value:                 VA(14) = .Cells(R + 3, 4).Value
        VA(15) = .Cells(R + 3, 6).Value:                 VA(16) = .Cells(R + 4, 2).Value
        VA(17) = .Cells(R + 4, 4).Value:                 VA(18) = .Cells(R + 4, 6).Value
        VA(19) = .Cells(R + 5, 2).Value:                 VA(20) = .Cells(R + 5, 4).Value
        VA(21) = .Cells(R + 5, 6).Value:                 VA(22) = .Cells(R + 6, 2).Value
             R = R + 7:   Sheet2.Cells(L, 9).Resize(, 14).Value = VA
        Loop While IsDate(.Cells(R, 1).Value)
                                                              R = R + 2:  If R > M Then Exit Sub
        Loop
    End With
End Sub 


Do you like it ? So thanks to click on bottom left star icon « Add Reputation » !

Thank you! This works perfectly and is way more elegant that what I had started to work out on my own.