Solved

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

Posted on 2012-03-13
10
192 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 41

Expert Comment

by:dlmille
Comment Utility
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
Comment Utility
Yes...
0
 

Author Comment

by:R B
Comment Utility
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
Comment Utility
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
Comment Utility
Great!!  Thank you!!
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
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.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

743 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

14 Experts available now in Live!

Get 1:1 Help Now