+ Reply to Thread
Results 1 to 8 of 8

Transpose Macro for single line consolidation

  1. #1
    Registered User
    Join Date
    02-23-2012
    Location
    TX
    MS-Off Ver
    Excel 2010
    Posts
    5

    Transpose Macro for single line consolidation

    Hello :)

    I am brand new to Excel macro programming - I am a SAS guy from experience.

    I have an interesting transpose question.

    I need my table to go from this:


    Acct Order_qty Order_amt Date
    1 15 $300 4/23/2007
    1 12 $300 5/21/2007
    1 13 $300 5/29/2007
    2 10 $300 7/15/2008
    2 9 $300 9/14/2008
    2 10 $300 3/16/2009
    3 17 $300 4/15/2007
    3 17 $300 9/13/2010

    To this:

    Acct Order_qty1 Order_qty2 Order_qty3 order_amt1 order_amt2 order_amt3 Date_1 Date_2 Date_3
    1 15 12 13 $300 $300 $300 4/23/2007 5/21/2007 5/29/2007
    2 10 9 10 $300 $300 $300 7/15/2008 9/14/2008 3/16/2009
    3 17 17 - $300 $300 - 4/15/2007 9/13/2010 -

    What I would really like is to consolidate each account number to one line and then transpose the corresponding information with the suffix at the end!

    If anyone can figure this out that would be great! Thanks guys!

    I've also attached the book in case the forum text doesn't work out.
    Attached Files Attached Files
    Last edited by cmross2010; 02-24-2012 at 11:55 AM.

  2. #2
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Transpose Macro for single line consolidation

    cmross2010,

    Welcome to the Excel Forum.

    Will the maximum number of Order_qty#'s always be 3?

    Or, could there more than 3?
    Have a great day,
    Stan

    Windows 10, Excel 2007, on a PC.

    If you are satisfied with the solution(s) provided, please mark your thread as Solved by clicking EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

  3. #3
    Registered User
    Join Date
    02-23-2012
    Location
    TX
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Transpose Macro for single line consolidation

    There could be any number. But the idea is to create a macro that can identify the end of one unique account number and then move to the next. So there could be one instance or up to 100 instances. Does that help?

  4. #4
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Transpose Macro for single line consolidation

    Hi

    So you would have to determine the ID with the most account numbers, and create headings to cover that number, then fill for all the others at the appropriate place?

    rylo

  5. #5
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Transpose Macro for single line consolidation

    Hi

    Hopefully
    Please Login or Register  to view this content.
    rylo

  6. #6
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Transpose Macro for single line consolidation

    cmross2010,


    Detach/open workbook ReorgData groups ABCD G thru P plus - cmross2010 - EF816419 - SDG16.xlsm and run the ReorgData macro.



    If you want to use the macro on another workbook:


    Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

    1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
    2. Open your workbook
    3. Press the keys ALT + F11 to open the Visual Basic Editor
    4. Press the keys ALT + I to activate the Insert menu
    5. Press M to insert a Standard Module
    6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
    7. Press the keys ALT + Q to exit the Editor, and return to Excel
    8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


    Please Login or Register  to view this content.

    Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


    Then run the ReorgData macro.
    Last edited by stanleydgromjr; 02-24-2012 at 09:48 AM.

  7. #7
    Registered User
    Join Date
    02-23-2012
    Location
    TX
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Transpose Macro for single line consolidation

    You guys are great!

    I modified it to handle more columns as well.

    Now I just need to go buy a book on VBA programming so that I can really learn this stuff. Any suggestions?

    Thanks!

  8. #8
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Transpose Macro for single line consolidation

    cmross2010,

    You are very welcome.

    Glad I could help.

    Thanks for the feedback.

    Come back anytime.


    Now I just need to go buy a book on VBA programming so that I can really learn this stuff. Any suggestions?

    Training / Books / Sites

    MrExcel's Products: Books, CDs, Podcasts Discuss topics related to Holy Macro! Products: Learn Excel from MrExcel, VBA and Macros for Microsoft Excel,Holy Macro! It's 2500 VBA Examples CD, Guerilla Data Analysis Using Microsoft Excel and Excel Knowledge Base CD and the MrExcel Podcasts.
    http://www.mrexcel.com/forum/forumdisplay.php?f=19

    How to Learn to Write Macros
    http://articles.excelyogi.com/playin...ba/2008/10/27/

    How to use the macro recorder
    http://articles.excelyogi.com/

    Click here and scroll down to Getting Started with VBA.
    http://www.datapigtechnologies.com/ExcelMain.htm

    If you are serious about learning VBA try
    http://www.add-ins.com/vbhelp.htm

    Excel Tutorials and Tips - VBA - macros - training
    http://www.mrexcel.com/articles.shtml

    See David McRitchie's site if you just started with VBA
    http://www.mvps.org/dmcritchie/excel/getstarted.htm

    What is a Visual Basic Module?
    http://www.emagenit.com/VBA%20Folder...vba_module.htm

    Debra Dalgleish has some notes how to implement macros here:
    http://www.contextures.com/xlvba01.html

    David McRitchie has an intro to macros:
    http://www.mvps.org/dmcritchie/excel/getstarted.htm

    Ron de Bruin's intro to macros:
    http://www.rondebruin.nl/code.htm

    Creating custom functions
    http://office.microsoft.com/en-us/ex...117011033.aspx

    Writing Your First VBA Function in Excel
    http://www.exceltip.com/st/Writing_Y...Excel/631.html

    Where to paste code in VBE VBA
    Introducing the Excel VBA Editor
    http://www.ask.com/web?qsrc=2417&o=1...cel+VBA+Editor

    VBA for Excel (Macros)
    http://www.excel-vba.com/excel-vba-contents.htm

    VBA Lesson 11: VBA Code General Tips and General Vocabulary
    http://www.excel-vba.com/vba-code-2-1-tips.htm

    Excel VBA -- Adding Code to a Workbook
    http://www.contextures.com/xlvba01.html

    http://www.excel-vba.com/
    http://www.mvps.org/dmcritchie/excel/getstarted.htm
    http://www.exceltip.com/excel_links.html

    (livelessons video)
    Excel VBA and Macros with MrExcel
    ISBN: 0-7897-3938-0
    http://www.amazon.com/Excel-Macros-M...7936479&sr=1-1

    Learn to debug:
    http://www.cpearson.com/excel/debug.htm

    Excel Tutorials / Video Tutorials - Functions
    http://www.contextures.com/xlFunctions02.html

    http://www.xl-central.com/index.html

    http://www.datapigtechnologies.com/ExcelMain.htm

    Cascading queries

    http://www.tushar-mehta.com/excel/ne...ing_dropdowns/

    Excel VLOOKUP Function and VLOOKUP Example
    http://www.contextures.com/xlFunctions02.html

    INDEX MATCH - Excel Index Function and Excel Match Function
    http://www.contextures.com/xlFunctions03.html

    http://www.contextures.com/xlDataVal02.html
    http://www.contextures.com/xlDataVal05.html
    http://www.contextures.com/xlDataVal08.html#Larger

    Excel Data Validation - Add New Items
    http://www.contextures.com/excel-dat...ation-add.html

    Programming The VBA Editor - Created by Chip Pearson at Pearson Software Consulting LLC
    This page describes how to write code that modifies or reads other VBA code.
    http://www.cpearson.com/Excel/vbe.aspx

    Locating files containing VBA
    Searching Files in Subfolders for VBA code string:
    http://www.dailydoseofexcel.com/arch...a-code-string/

    http://www.pcreview.co.uk/forums/thread-978054.php

    Excel 2003 Power Programming with VBA (Excel Power Programming With Vba)
    by John Walkenbach

    VBA and Macros for Microsoft Excel, by Bill Jelen "Mr.Excel" and Tracy Syrstad

    Excel Hacks 100 Industrial-Strength Tips & Tools, by David & Traina Hawley

    VBA and Macros for Microsoft Excel 2007, by Bill Jelen "Mr.Excel" and Tracy Syrstad

    Excel 2007 Book: you can try this...there is a try before you buy ebook available at this link…
    http://www.mrexcel.com/learnexcel2.shtml

    Professional Excel Development
    by Stephen/ Bovey, Rob/ Green, John Bullen (Paperback - Feb 11, 2005)

    Excel 2002 VBA: Programmers Reference
    by Rob Bovey, Stephen Bullen, John Green, and Robert Rosenberg (Paperback - Sep 26, 2001)

    VB & VBA in a Nutshell: The Language
    (http://www.amazon.co.uk/VB-VBA-Nutsh...4671189&sr=1-2)

    Writing Excel Macros with VBA
    (http://www.amazon.co.uk/Writing-Exce...4671189&sr=1-3)

    User Form Creation
    http://www.contextures.com/xlUserForm01.html

    DonkeyOte: My Recommended Reading
    Volatility
    http://www.decisionmodels.com/calcsecretsi.htm

    Sumproduct
    http://www.xldynamic.com/source/xld.SUMPRODUCT.html
    http://www.xldynamic.com/source/xld.SUMPRODUCT.html

    Arrays
    http://www.xtremevbtalk.com/showthread.php?t=296012

    Pivot Intro
    http://peltiertech.com/Excel/Pivots/pivotstart.htm

    Sync Pivot Tables
    http://www.mrexcel.com/forum/showthr...g+pivot+tables
    Check out rorya's post at the very bottom of this link. It should do the trick:
    http://www.experts-exchange.com/Soft..._22844558.html
    Multiple pivot tables 1 filter to control all
    http://www.excelforum.com/excel-prog...ntrol-all.html

    Email from XL - VBA
    http://www.rondebruin.nl/sendmail.htm

    Outlook VBA
    http://www.outlookcode.com/article.aspx?ID=40

    Function Dictionary
    http://www.xlfdic.com/

    Function Translations
    http://www.piuha.fi/excel-function-name-translation/

    Dynamic Named Ranges
    http://www.contextures.com/xlNames01.html

    How to create Excel Dashboards
    http://www.contextures.com/excel-dashboards.html
    http://chandoo.org/wp/excel-dashboards/
    http://chandoo.org/wp/management-dashboards-excel/
    http://www.exceldashboardwidgets.com/

    Excel Dashboard / Scorecard Ebook
    http://www.qimacros.com/excel-dashboard-scorecard.html

    Templates
    http://www.cpearson.com/Excel/Topic.aspx
    http://www.contextures.com/excel-tem...lf-scores.html

    Mike Alexander from Data Pig Technologies
    Excel 2007 Dashboards & Reports For Dummies

    Date & Time stamping:
    http://www.mcgimpsey.com/excel/timestamp.html

    Get Formula / Formats thru custom functions:
    http://dmcritchie.mvps.org/excel/formula.htm#GetFormat

    A nice informative MS article "Improving Performance in Excel 2007"
    http://msdn.microsoft.com/en-us/library/aa730921.aspx

    Progress Meters
    http://www.andypope.info/vba/pmeter.htm
    http://www.xcelfiles.com/ProgressBar.html

    And, as your skills increase, try answering posts on sites like:
    http://www.mrexcel.com
    http://www.excelforum.com
    http://www.ozgrid.com
    http://www.vbaexpress.com
    http://www.excelfox.com

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1