+ Reply to Thread
Results 1 to 9 of 9

Horizontal/Vertical Data

  1. #1
    Forum Contributor
    Join Date
    01-25-2021
    Location
    Atlanta, Georgia
    MS-Off Ver
    365
    Posts
    131

    Horizontal/Vertical Data

    I have been using a messy query report for over 6 months and am finally convinced this was never my best approach. So this post is to ask the community for feedback.

    I manage a small company that supports people with developmental disabilities. I pull medical information daily on hundreds of individuals for my managers to review. The attached is a small sample of a report but there are actually 60 categories (columns) , which always remain the same. Report pulls data from an external source via power query. Horizontal Report can be difficult to read since its runs so many columns across. So I transpose it to a Vertical layout in power query. Of course this has caused lots of issues upon refresh with formatting, which does not always carry over to new columns.

    Questions:
    1. Is transposing data in power query the best approach to get my data to lay out vertically?
    2. Would pivot table produce similar/better results?
    3. Is there a way to carry over formatting rules to new adjacent columns after refresh.

    Of course all solutions must be dynamic since hundreds of entries change often. Thanks so much for all responses!
    Attached Files Attached Files
    Last edited by JyothiGrace; 03-10-2022 at 10:58 AM.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2302
    Posts
    38,619

    Re: Horizontal/Vertical Data

    With O365, using PQ (in my opinion) is crazy (unless i have misunderstood!!). You can do it all with a one-cell formula:

    In B1:

    =TRANSPOSE(Horizontal!A2:K5)

    Job done...
    Attached Files Attached Files
    Glenn



  3. #3
    Forum Contributor
    Join Date
    01-25-2021
    Location
    Atlanta, Georgia
    MS-Off Ver
    365
    Posts
    131

    Re: Horizontal/Vertical Data

    Thanks Glenn but just to clarify, Horizontal tab is what I begin with and Vertical tab is what I want to end up with. I see this is not dynamic as more rows get added to Horizontal tab table.

    I don't want managers to have to strip output of its formulas.......hence the use of PQ.

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2302
    Posts
    38,619

    Re: Horizontal/Vertical Data

    Did you even open the file I attached? The formula delivers the vertical format!!!! A Named range can be used to select the appropriate number of rows in horizonatal format....

  5. #5
    Forum Contributor
    Join Date
    01-25-2021
    Location
    Atlanta, Georgia
    MS-Off Ver
    365
    Posts
    131

    Re: Horizontal/Vertical Data

    I see it but formatting does not carry over to the next column. Also can Transpose data be put into a table so users can use AutoFilter?
    Attached Files Attached Files

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2302
    Posts
    38,619

    Re: Horizontal/Vertical Data

    I'm away fom the PC now for 10 hours or so.. However, you can copy/paste formatting from columnB to wherever you expect the data to end. Do that... then add more rows.
    Until Named ranges are added, you'll have to change the formula range manually.

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2302
    Posts
    38,619

    Re: Horizontal/Vertical Data

    Re transpose/filter... try it yourself. On my phone... I can't.

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2302
    Posts
    38,619

    Re: Horizontal/Vertical Data

    So.

    1. I turned the data in horizontal into a Table.

    2. I formatted columnc C to AZ to match column B.

    3. I formatted the comments row as : 0;-0;;@ to hide zeros when comments are absent.

    4. The formula (which now resides in VERTICAL A1) reads:
    =TRANSPOSE(Table1[#All])

    Fully dynamic. Formats perfectly. Try it.
    Attached Files Attached Files

  9. #9
    Valued Forum Contributor
    Join Date
    12-09-2014
    Location
    Trakai, Lithuania
    MS-Off Ver
    2016
    Posts
    933

    Re: Horizontal/Vertical Data

    PQ
    Please Login or Register  to view this content.
    Attached Files Attached Files

+ 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: 16
    Last Post: 06-01-2017, 06:01 PM
  2. Horizontal Data to Vertical
    By mmagnin in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 01-18-2014, 12:45 AM
  3. [SOLVED] Horizontal data in vertical
    By ashfaquebwd in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-10-2013, 03:50 AM
  4. Vertical to Horizontal data help!
    By hbrown78 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-05-2012, 09:57 PM
  5. [SOLVED] Data horizontal to vertical
    By sureshpunna in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-27-2012, 03:36 AM
  6. [SOLVED] Vertical to Horizontal data
    By trosasco in forum Excel General
    Replies: 5
    Last Post: 06-15-2012, 08:48 AM
  7. Copying data horizontal to vertical
    By blander in forum Excel General
    Replies: 3
    Last Post: 09-28-2007, 04:55 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