+ Reply to Thread
Results 1 to 5 of 5

Data Table Converting in one column

  1. #1
    Registered User
    Join Date
    05-16-2014
    MS-Off Ver
    Excel 2003
    Posts
    11

    Data Table Converting in one column

    Hi,
    I have a data table and i want it to rearrange it so that all data that was in horizontal rows now would be in one vertical column,
    when data from one row would end the data from second row would continue.

    Data:
    1 4 5 6
    2 3 4 5
    ...

    In the end it should be:
    1
    4
    5
    6
    2
    3
    4
    5
    ...

    In the attached file I tried to do that manually, but as i have 11 thousand rows it would take a century to do that, is there a way how to make it easier?
    Thank you.
    Attached Files Attached Files

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2405 Win 11 Home 64 Bit
    Posts
    23,885

    Re: Data Table Converting in one column

    Try this:

    Please Login or Register  to view this content.
    How to install your new code
    1. Copy the Excel VBA code
    2. Select the workbook in which you want to store the Excel VBA code
    3. Press Alt+F11 to open the Visual Basic Editor
    4. Choose Insert > Module
    5. Edit > Paste the macro into the module that appeared
    6. Close the VBEditor
    7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

    To run the Excel VBA code:
    1. Press Alt-F8 to open the macro list
    2. Select a macro in the list
    3. Click the Run button
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Valued Forum Contributor abduljaleel.mca's Avatar
    Join Date
    02-13-2013
    Location
    Chennai, India
    MS-Off Ver
    MS 365 Business
    Posts
    326

    Re: Data Table Converting in one column

    Try this...
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    05-16-2014
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Data Table Converting in one column

    Thanks..

  5. #5
    Valued Forum Contributor
    Join Date
    09-25-2011
    Location
    Eastbourne, England
    MS-Off Ver
    Excel 2010
    Posts
    1,089

    Re: Data Table Converting in one column

    @ abduljaleel.mca

    Nice one - except column Y gets overwritten. You need an offset when finding the next available row as in
    Please Login or Register  to view this content.
    Regards
    Alastair

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 3
    Last Post: 12-16-2013, 01:34 AM
  2. Converting a 2-row table into a multi-column table
    By kle in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-24-2013, 08:49 PM
  3. [SOLVED] converting matrix data into a table
    By Red fuji in forum Excel General
    Replies: 16
    Last Post: 06-28-2012, 12:21 PM
  4. [SOLVED] Converting data in a table into a list
    By Speshul in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-25-2012, 03:13 PM
  5. Converting column to rows -not pivot table
    By jartzh in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-05-2008, 08:28 AM

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