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?
 
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
 
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
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
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
 
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
 
R BAuthor Commented:
Great!!  Thank you!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.