+ Reply to Thread
Results 1 to 9 of 9

Convert Horizontal Data to a Vertical Format

  1. 04-28-2018, 09:26 AM #1
    skifiend
    skifiend is offline
    Registered User
    Join Date
    10-29-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    4

    Question Convert Horizontal Data to a Vertical Format

    I have a table of data such that specific columns of the information needs to be converted from a horizontal layout and inserted below the initial row. To make things more complex any column with a value of zero needs to be ignored and each row may have a different column with a zero.

    This query is super close, but for some reason when run it on this data set it is not picking up all the horizontal data. For some reason it is stopping at column "S" rather than going to column "CZ". Also in rows where there are zeros in column "B" it does not pick up the account number and just adds the Revenue Code and Charges to the name above it (see where account 123123141 was skipped, but the object numbers were added to 123123140).

    Please Login or Register  to view this content.
    Raw Data:
    Data Set (horizontal).png

    Post Fix (incomplete):
    Post Fix Data Set (Vertical).png
    Attached Files Attached Files
    Last edited by jeffreybrown; 04-28-2018 at 09:55 AM. Reason: Please use code tags!
    Reply With QuoteRegister To Reply
  2. 04-28-2018, 10:52 AM #2
    alansidman
    alansidman is offline
    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,883

    Re: Convert Horizontal Data to a Vertical Format

    Try this loop. Change the sheet names to what is in your file as noted

    Please Login or Register  to view this content.
    Last edited by alansidman; 04-28-2018 at 10:55 AM.
    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
    Reply With QuoteRegister To Reply
  3. 04-28-2018, 10:57 AM #3
    Fluff13
    Fluff13 is offline
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,080

    Re: Convert Horizontal Data to a Vertical Format

    Try 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  4. 04-28-2018, 12:07 PM #4
    skifiend
    skifiend is offline
    Registered User
    Join Date
    10-29-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Convert Horizontal Data to a Vertical Format

    Awesome! This is great.. I am not sure how to translate this entire piece (itovermyhead), but is there a way to add in the headers of all the values into column "B" to line up with its value?

    Thanks for this alansidman!!
    Reply With QuoteRegister To Reply
  5. 04-28-2018, 12:13 PM #5
    skifiend
    skifiend is offline
    Registered User
    Join Date
    10-29-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Convert Horizontal Data to a Vertical Format

    Fluff13, Thank you! this totally fixed the piece where the query was not picking up all the values to the right. Any chance you see anything that is causing the account 123123141 to be skipped?

    Thanks again for that solution! Much appreciated!
    Reply With QuoteRegister To Reply
  6. 04-28-2018, 01:01 PM #6
    Fluff13
    Fluff13 is offline
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,080

    Re: Convert Horizontal Data to a Vertical Format

    It's not actually being skipped, but along with 123123146 & 123123147 the first value is 0 & therefore the row containing the Account# is deleted.
    One option is to add the part in blue 
    Please Login or Register  to view this content.
    which will copy the account numbers to every row.
    Reply With QuoteRegister To Reply
  7. 04-28-2018, 01:58 PM #7
    sandy666
    sandy666 is offline
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: Convert Horizontal Data to a Vertical Format

    another way with PowerQuery (with/without PivotTable)
    account *41 is filtered
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 04-28-2018, 02:14 PM #8
    skifiend
    skifiend is offline
    Registered User
    Join Date
    10-29-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Convert Horizontal Data to a Vertical Format

    Fluff13 and alansidman.. THANK YOU!! This is exactly what I needed. I have been struggling with this for weeks.
    Reply With QuoteRegister To Reply
  9. 04-28-2018, 02:21 PM #9
    Fluff13
    Fluff13 is offline
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,080

    Re: Convert Horizontal Data to a Vertical Format

    Glad we could help & thanks for the feedback
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] convert the horizontal data to vertical forum
    By Pritpal Singh in forum Excel General
    Replies: 1
    Last Post: 04-06-2013, 03:35 AM
  2. [SOLVED] Macro to convert vertical data in horizontal format
    By ajang in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-27-2013, 06:03 PM
  3. [SOLVED] Convert Data Vertical to Horizontal
    By dextryn in forum Excel General
    Replies: 2
    Last Post: 02-19-2013, 07:27 PM
  4. convert data horizontal to vertical
    By vorabha in forum Excel General
    Replies: 8
    Last Post: 02-10-2013, 03:53 PM
  5. convert vertical data to horizontal
    By syuk225 in forum Excel General
    Replies: 3
    Last Post: 06-14-2012, 03:58 AM
  6. Convert data From Vertical to Horizontal Format
    By reyrey in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-20-2011, 03:52 PM
  7. [SOLVED] Convert Vertical row data into Horizontal
    By ajang in forum Excel General
    Replies: 8
    Last Post: 11-01-2010, 01:28 AM
  8. Convert Excel From Horizontal To Vertical Format
    By masrizam in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 04-13-2009, 02:29 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1