Results 1 to 6 of 6

Turn a 2 column vertical list in to a multi colum horizontal grid - Macro required I think

This is a discussion on Turn a 2 column vertical list in to a multi colum horizontal grid - Macro required I think within the Excel Questions forums, part of the Question Forums category; Hi, I need to reformat a list downloaded from Access (now in excel) repeatidly, and the list is large. I ...

  1. #1
    New Member
    Join Date
    May 2012
    Posts
    3

    Default Turn a 2 column vertical list in to a multi colum horizontal grid - Macro required I think

    Hi,

    I need to reformat a list downloaded from Access (now in excel) repeatidly, and the list is large.

    I currently have a list with 2 columns, "Investor_Group_Name" and "Investor_LE".

    There are thousands of "investor_le", but a number of different LE's will have the exact same Group Name...however the number of LE's any one Group may have varies from 1 to c.30.

    I.e.
    GroupLE
    DogJack Russel
    DogBloodhound
    DogBritish bull dog
    CatTabby
    FishParana
    Fishdolphin
    DogLabrador



    I want to be able to change it so there is only one version of the group name, and all of the related LE's are put horizontally next to it. i.e.

    GroupLE
    DogJack RusselBloodhoundBritish bull dogLabrador
    CatTabby
    FishParanadolphin


    Can Anyone help...oh and I have only just started playing with Macro's this morning....

    Many thanks,

    Hamish

  2. #2
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    16,875

    Default Re: Turn a 2 column vertical list in to a multi colum horizontal grid - Macro required I think

    needexcelhlp,

    Welcome to the MrExcel forum.

    What version of Excel are you using?


    Sample raw data:


    Sheet1

     ABCDEFGHI
    1GroupLE       
    2DogJack Russel       
    3DogBloodhound       
    4DogBritish bull dog       
    5CatTabby       
    6FishParana       
    7Fishdolphin       
    8DogLabrador       
    9         


    Excel tables to the web >> Excel Jeanie HTML 4




    After the macro:


    Sheet1

     ABCDEFGHI
    1GroupLE  GroupLE   
    2DogJack Russel  CatTabby   
    3DogBloodhound  DogBloodhoundBritish bull dogJack RusselLabrador
    4DogBritish bull dog  FishdolphinParana  
    5CatTabby       
    6FishParana       
    7Fishdolphin       
    8DogLabrador       
    9         


    Excel tables to the web >> Excel Jeanie HTML 4




    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.


    Code:
    Option Explicit
    Sub ReorgData()
    ' hiker95, 05/02/2012
    ' http://www.mrexcel.com/forum/showthread.php?t=632925
    Dim r As Long, lr As Long, nr As Long, n As Long
    Dim a
    Application.ScreenUpdating = False
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    a = Range("A1:B" & lr)
    Range("A2:B" & lr).Sort key1:=Range("A2"), order1:=1, Key2:=Range("B2"), Order2:=1
    With Cells(1, 5).Resize(, 2)
      .Value = Cells(1, 1).Resize(, 2).Value
      .Font.Bold = True
    End With
    For r = 2 To lr
      n = Application.CountIf(Columns(1), Cells(r, 1).Value)
      nr = Range("E" & Rows.Count).End(xlUp).Offset(1).Row
      If n = 1 Then
        Cells(nr, 5).Resize(, 2).Value = Cells(r, 1).Resize(, 2).Value
      ElseIf n > 1 Then
        Cells(nr, 5).Value = Cells(r, 1).Value
        Cells(nr, 6).Resize(, n).Value = Application.Transpose(Cells(r, 2).Resize(n).Value)
      End If
      r = r + n - 1
    Next r
    Range("A1").Resize(UBound(a, 1), UBound(a, 2)).Value = a
    ActiveSheet.UsedRange.Columns.AutoFit
    Application.ScreenUpdating = True
    End Sub

    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.
    Have a great day,
    hiker95

    Windows 10, Excel 2007, on a PC.

  3. #3
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    16,875

    Default Re: Turn a 2 column vertical list in to a multi colum horizontal grid - Macro required I think

    needexcelhlp,

    oh and I have only just started playing with Macro's this morning....

    Training / Books / Sites as of 05/02/2012

    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

    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

    Here's a good primer on the scope of variables.
    Scope Of Variables And Procedures

    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

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

    Creating An XLA Add-In For Excel, Writing User Defined Functions In VBA
    http://www.cpearson.com/excel/createaddin.aspx

    How do I create a PERSONAL.XLS(B) or Add-in
    http://www.rondebruin.nl/personal.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

    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

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

    How To: Assign a Macro to a Button or Shape
    http://peltiertech.com/WordPress/how...tton-or-shape/

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

    When To Use a UserForm & What to Use a UserForm For
    http://www.ozgrid.com/Excel/free-tra...ba2lesson2.htm

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

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

    Excel Data Validation
    http://www.contextures.com/xlDataVal08.html#Larger
    http://www.contextures.com/excel-dat...ation-add.html

    Your Quick Reference to Microsoft Excel Solutions
    http://www.xl-central.com/index.html

    New! Excel Recorded Webinars
    http://www.datapigtechnologies.com/ExcelMain.htm

    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

    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ů

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

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

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

    Sumproduct
    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

    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/
    http://www.andypope.info/charts/gauge.htm

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

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

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

    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
    Have a great day,
    hiker95

    Windows 10, Excel 2007, on a PC.

  4. #4
    New Member
    Join Date
    May 2012
    Posts
    3

    Default Re: Turn a 2 column vertical list in to a multi colum horizontal grid - Macro required I think

    Hiker 95...

    I think you are a Genius...seems to have worked! I'm currently on Excel 2003, but will shortly be moving on to excel 2007.

    Cheers for all the links...A LOT of reading there for me by the looks of things!

    Thank you very much!

    Hamish

  5. #5
    New Member
    Join Date
    May 2012
    Posts
    3

    Default Re: Turn a 2 column vertical list in to a multi colum horizontal grid - Macro required I think

    Hiker95,

    I have realised that I need the macro to be more complicated!

    I also need it to:
    1-Group the seperate LE's so than I can make them in to a list
    2-Name this grouping the Parents name

    I.e.

    Parana and Dolphin, when Highligheted would be known together as "Fish". Obviously however, the number of LE's varies, but I only want the group to be as long as the number of LE's, not having any blanks!

    Finally, a more excel related question. I am making conditional drop down lists from this information, but I have to scroll through the parent company names. Is there anyway that when I click the down facing arrow to open the drop down option I can type the first letter and it moves to that section of the list? Like they have on internet sites for flight bookings etc.

    Cheers,

    Hamish

  6. #6
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    16,875

    Default Re: Turn a 2 column vertical list in to a multi colum horizontal grid - Macro required I think

    needexcelhlp,

    I also need it to:
    1-Group the seperate LE's so than I can make them in to a list
    2-Name this grouping the Parents name

    I.e.

    Parana and Dolphin, when Highligheted would be known together as "Fish". Obviously however, the number of LE's varies, but I only want the group to be as long as the number of LE's, not having any blanks!

    Finally, a more excel related question. I am making conditional drop down lists from this information, but I have to scroll through the parent company names. Is there anyway that when I click the down facing arrow to open the drop down option I can type the first letter and it moves to that section of the list? Like they have on internet sites for flight bookings etc.

    To continue we will need screenshots fo your raw data worksheet, and screenshots of the resutls you are looking for, either in the same worksheet, or in a different worksheet.


    To post your data, you can download and install one of the following two programs:
    Excel Jeanie
    MrExcel HTML Maker

    Or, when using Internet Explorer, just put borders around your data in Excel and copy those cells into your post.


    If you are not able to give us screenshots:
    You can upload your workbook to Box Net,

    sensitive data scrubbed/removed/changed

    mark the workbook for sharing
    and provide us with a link to your workbook.
    Have a great day,
    hiker95

    Windows 10, Excel 2007, on a PC.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com