+ Reply to Thread
Results 1 to 11 of 11

Converting horizontal data to vertical data with multilple rows

  1. #1
    Registered User
    Join Date
    10-09-2008
    Location
    London
    MS-Off Ver
    2003, 2007
    Posts
    9

    Converting horizontal data to vertical data with multilple rows

    I would be grateful for help please to reformat a table in which data for each named person is presented in one row with mutiple columns into a table in which each named person has multiple rows and one column of data. The solution to to this has eluded me so far.

    The attached example shows before and after. Any help would be very much appreciated.
    Attached Files Attached Files

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Converting horizontal data to vertical data with multilple rows

    I nice quick and easy way of doing this which avoids both expensive formulae and VBA is to use a Multi Consolidation Pivot Table on the source data, drill into the Grand Total of the resulting PT and you get the source data transposed... see attached.

    Re: attachment - given the drill through sheet is not tied to any data you can then manipulate that without fear of affecting any other data in the file, eg to get rid of blank values:

    Highlight column C -> F5 -> Special -> Blanks -> OK -> ALT + E -> D -> ALT + R -> OK

    Job done, nice and quick (& clean).
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    10-09-2008
    Location
    London
    MS-Off Ver
    2003, 2007
    Posts
    9

    Re: Converting horizontal data to vertical data with multilple rows

    DonkeyOte, many thanks for your very quick response. Pivot Tables are not something I have used before so I have got some learning to do there. I don't understand, though, what you mean by 'drill into the Grand Total of the resulting Pivot Table' Sorry to come back to you with this question. Kind regards

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Converting horizontal data to vertical data with multilple rows

    If you look at the Attachment I posted you will see three sheets

    Sheet1 contains your base data (A6:F12)
    Sheet3 contains a Multi Consolidation PT based off Sheet1!A6:F12

    One of the more wonderful attributes of a PT is the ability to "drill down" into the data ie if you double click on any number in the Data section of the PT (ie B5:G11 in the example) you will get the transactional break down of how that value is comprised on an entirely new sheet.

    Thus if you double click on the "Grand Total" figure in G11 if follows that you will in fact get all of the transactions returned that make up the PT (this is represented in the file by Sheet4).
    Using this Multi Consolidation PT approach means that when you "drill down" the resulting transactions are transposed from their original layout.

    I hope that helps.

  5. #5
    Registered User
    Join Date
    10-09-2008
    Location
    London
    MS-Off Ver
    2003, 2007
    Posts
    9

    Re: Converting horizontal data to vertical data with multilple rows

    DonkeyOte, many thanks for your further response. I have now managed to apply what you told me to my own data files and the result is just what I wanted. I am so grateful for the information and help you gave me, thank you very much. Kind regards

  6. #6
    Registered User
    Join Date
    08-23-2012
    Location
    ct
    MS-Off Ver
    excel 2007
    Posts
    1

    Re: Converting horizontal data to vertical data with multilple rows

    How do you get the Pivot Table field to be "Column" rather than the individual subjects?

  7. #7
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,211

    Re: Converting horizontal data to vertical data with multilple rows

    You would likely get more replies if you started your own thread asking a new question, as this thread is almost 4 years old and has been solved already.
    You should hit F5, because chances are I've edited this post at least 5 times.
    Example of Array Formulas
    Quote Originally Posted by Jacc View Post
    Sorry, your description makes no sense. I just made some formula that looks interesting cause I had nothing else to do.
    Click the * below on any post that helped you.

  8. #8
    Registered User
    Join Date
    08-25-2010
    Location
    Mumbai
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Converting horizontal data to vertical data with multilple rows

    Friends,

    I have data with many rows (approx 1000) as follows

    A B D C
    USA JAPAN CHINA THAILAND


    I want to convert the data to

    1 USA
    2 JAPAN
    3 CHINA
    4 THAILAND

    Pls advice a macro which would help me to convert the data in loop until i find records in sheet. All the data will go below the above row last record

  9. #9
    Banned User!
    Join Date
    10-29-2012
    Location
    Europe
    MS-Off Ver
    2013, 2016
    Posts
    318

    Re: Converting horizontal data to vertical data with multilple rows

    You can solve without VBA code.

    Select dates from B1 to last column (Ctrl + Shift + right arrow), then right-click to the selection and choose COPY, right click in cell A2 and choose from the context menu, TRANSPOSE.

  10. #10
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Converting horizontal data to vertical data with multilple rows

    see the attached file
    Attached Files Attached Files
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  11. #11
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Converting horizontal data to vertical data with multilple rows

    it can be simplified with helper column

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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