Excel 2007: How can I easily change rows of data to populate into columns?

I have one invoice with several lines going down.  I have a charge-card bill that has several line items.  I've downloaded it and added columns to encode it out to enter to our Financial/Accounting Software.   For me to upload it - I need to have all of that data reading on "one line" - i.e.  all in columns (not rows).  

What is the simplest way for me to do this? - without me getting into any elaborate pivoting process.  Or - is pivoting the only way to do it?

I want to auto load it - and - need to have all the data that is now in one column for account number, $ amount,  charge-description :  to go into the same row,  horizontally.  Now, the whole file is vertically place with each row having the same invoice number, same vendor name, and then one line item (account to charge, charge-vendor description, $ amount) per row.

Please assist.
Thank you,
RajB.
R BAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dlmilleCommented:
If a simple copy, select cell, pastespecial transpose won't work, can you share an example of the before and after?
0
R BAuthor Commented:
Here is a sample:  One Tab shows what I have, and the other tab shows a simulation of what I want to do automatedly.  Thank you...
0
R BAuthor Commented:
Here is a sample:  One Tab shows what I have, and the other tab shows a simulation of what I want to do automatedly.  Thank you...
Sample-Invoice---Vertical-Data-t.xlsx
0
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

dlmilleCommented:
You'll need VBA do do this.  Pivot table will not put it all on one row, but if you could, you'd still not get exactly that output.

Do you want a VBA macro?

Dave
0
R BAuthor Commented:
Yes... Surely - Dave... Can you advise me on how to do a VBA Macro for this?  I will have this scenario replicating several times during a week.  Thanks....
0
dlmilleCommented:
The key is:  Invoice Number      System Vendor ID      Invoice Date      Invoice Post Date

Correct?  For every unique combination of the 4 above, we want to add the charge date,item,amount,description until there is no more data for that combination.

Also, will the data be sorted?

Dave
0
R BAuthor Commented:
Yes...
0
R BAuthor Commented:
Hi, Dave...  Yes, we can have multiple charges on the same day.  Actually - the only unique fields would be "invoice number" and "vendor id".  The date of the invoice nor its post date are relavant.  So - the two unique items would be:  Vendor ID, and Invoice Number.  That's it.   Does that make the VBA Macro easier to create?
0
dlmilleCommented:
Ok.  Try this out.  The unique ID is actually the first 4 columns, as its the last 4 that are repeating, re: your sample output.

Here's your code:
Option Explicit
Private Safe_Charge_Date As Date
Private Safe_Charge_Item As String
Private Safe_Charge_Amt As Double
Private Save_Charge_Desc As String

Sub generateFlatTable()
Dim wkb As Workbook
Dim wks As Worksheet
Dim wksOut As Worksheet
Dim rngOut As Range
Dim rHeader As Range
Dim rng As Range
Dim r As Range
Dim myDict As Object
Dim i As Long
Dim lastCol As Long
Dim myKey As String

    Set wkb = ActiveWorkbook
    Set wks = wkb.ActiveSheet
    
    Set myDict = CreateObject("Scripting.Dictionary") 'holds unique values
    
    Set rHeader = wks.Range("A1", wks.Cells(1, wks.Columns.Count).End(xlToLeft))
    Set rng = wks.Range("A2", wks.Range("A" & wks.Rows.Count).End(xlUp))
    
    On Error Resume Next
    Set wksOut = wkb.Worksheets("Output")
    If Err.Number <> 0 Then
        Set wksOut = wkb.Worksheets.Add(after:=wks)
        wksOut.Name = "Output"
    End If
    On Error GoTo 0
    
    wksOut.Cells.Clear 'clear output tab
    
    Set rngOut = wksOut.Range("A2")
    i = -1
    For Each r In rng
        myKey = r.Value & "," & r.Offset(, 1).Value & "," & r.Offset(, 2).Value & "," & r.Offset(, 3).Value 'key is Invoice Number, Vendor ID, Inv Date, Inv Post Date
        If Not myDict.exists(myKey) Then
            i = i + 1
            myDict.Add myKey, Nothing
            'output the line, so far
            rngOut.Offset(i, 0).Resize(, rHeader.Columns.Count).Value = r.Resize(, rHeader.Columns.Count).Value
        Else
            'just output what's found as key matches (assumed sorted data)
            lastCol = wksOut.Cells(rngOut.Offset(i, 0).Row, wksOut.Columns.Count).End(xlToLeft).Column
            wksOut.Cells(rngOut.Offset(i, 0).Row, lastCol + 1).Resize(, 4).Value = r.Offset(, 4).Resize(, 4).Value
        End If
    Next r
    
    'finalize header
    wksOut.Range("A1").Resize(1, rHeader.Columns.Count).Value = rHeader.Value
    lastCol = wksOut.Cells(rngOut.Row, wksOut.Columns.Count).End(xlToLeft).Column
    wksOut.Range("E1:H1").Copy
    wksOut.Range("I1", wksOut.Cells(1, lastCol)).PasteSpecial
    Application.CutCopyMode = False
            
    With wksOut.Range("A1", wksOut.Cells(1, lastCol))
        .EntireColumn.AutoFit
        .Font.Bold = True
    End With
    
    myDict.RemoveAll
    Set myDict = Nothing
    
End Sub

Open in new window


see attached.

Dave
Sample-Invoice---Vertical-Data-r.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
R BAuthor Commented:
Great!!  Thank you!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.