+ Reply to Thread
Results 1 to 7 of 7

Convert data in columns to rows...

  1. #1
    Registered User
    Join Date
    09-13-2021
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    3

    Post Convert data in columns to rows...

    I have a simple table consisting of a a column containing unique ID's followed by many columns with with data. I would like to convert the data in the additional columns into sequential rows. Attached is a sample spreadsheet of what I am trying to accomplish:

    Any idea if this is possible in Excel?

    Thanks in advance,

    MG
    Attached Files Attached Files

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Convert data in columns to rows...

    Welcome to the forum MapGuy99.

    I notice you have posted in 'Charting and Pivots'. Is that a requirement.
    Yes it is possible with formula(s).
    I am working with the assumption that you wish to work directly from data in columns A:C and not from an intermediate step in E:F.

    Is that correct?
    Dave

  3. #3
    Registered User
    Join Date
    09-13-2021
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    3

    Re: Convert data in columns to rows...

    Yes, A:C represents the source data, and E:F is the desired result.

    Regards,

    MG

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

    Re: Convert data in columns to rows...

    Can be done with Power Query. Unpivot your data.

    Highlight your table. Bring into POwer Query Editor. Highlight column 1 and on the Transform Table, select Unpivot. Unpivot other Data. Highlight Attribute Column. Right Click and Remove. Highlight Value Column and rename Attribute. Close and Load

    Please Login or Register  to view this content.
    Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

    It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

    - Follow this link to learn how to install Power Query in Excel 2010 / 2013.

    - Follow this link for an introduction to Power Query functionality.

    - Follow this link for a video which demonstrates how to use Power Query code provided.
    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

  5. #5
    Registered User
    Join Date
    09-13-2021
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    3

    Thumbs up Re: Convert data in columns to rows...

    Brilliant, works like a charm.

    Thanks so much.

    MG

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Convert data in columns to rows...

    I was understanding you wanted output as in Sheet2 A2:F3. My bad.

    For E:F output .....
    If you are interested in formula way try this formula in E2 and fill down until you get blanks.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Then F2
    Formula: copy to clipboard
    Please Login or Register  to view this content.

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

    Re: Convert data in columns to rows...

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

+ 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: 1
    Last Post: 03-06-2013, 01:45 AM
  2. Excel 2007 : Convert data from columns to rows
    By thandanit in forum Excel General
    Replies: 1
    Last Post: 03-17-2011, 03:41 AM
  3. Convert a set of data from rows to columns
    By naferterri in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-19-2010, 08:33 PM
  4. Convert data from columns to rows
    By ClikClak in forum Excel General
    Replies: 5
    Last Post: 07-19-2010, 12:26 PM
  5. convert data from columns to rows
    By maacmaac in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-03-2009, 11:14 AM
  6. Convert Columns to rows with DATA
    By raed_237 in forum Excel General
    Replies: 6
    Last Post: 08-05-2008, 04:18 AM
  7. [SOLVED] Convert data from rows to columns
    By boksic in forum Excel General
    Replies: 4
    Last Post: 07-04-2005, 07:05 AM

Tags for this Thread

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