Solved

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

Posted on 2012-03-13
10
197 Views
Last Modified: 2012-03-28
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.
0
Comment
Question by:R B
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
10 Comments
 
LVL 42

Expert Comment

by:dlmille
ID: 37715486
If a simple copy, select cell, pastespecial transpose won't work, can you share an example of the before and after?
0
 

Author Comment

by:R B
ID: 37715898
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
 

Author Comment

by:R B
ID: 37715913
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 42

Expert Comment

by:dlmille
ID: 37716003
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
 

Author Comment

by:R B
ID: 37716008
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
 
LVL 42

Expert Comment

by:dlmille
ID: 37716080
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
 

Author Comment

by:R B
ID: 37716086
Yes...
0
 

Author Comment

by:R B
ID: 37716126
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
 
LVL 42

Accepted Solution

by:
dlmille earned 500 total points
ID: 37716485
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
 

Author Closing Comment

by:R B
ID: 37778179
Great!!  Thank you!!
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question