Solved

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

Posted on 2012-03-13
10
193 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
  • 6
  • 4
10 Comments
 
LVL 41

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
 
LVL 41

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 41

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 41

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

920 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now