+ Reply to Thread
Results 1 to 9 of 9

Convert Vertical row data into Horizontal

  1. #1
    Registered User
    Join Date
    10-13-2010
    Location
    Miami, US
    MS-Off Ver
    Excel 2007
    Posts
    83

    Convert Vertical row data into Horizontal

    Hi,

    I have a data set from a study with 2023 participants (Sample workbook attached).

    There are five recorded variables (C_ID, E_NAME, C_NAME, P_NAME and EVENT_DT). These variables are in the vertical order. I want them Horizontally. I am aware of transpose function but unfortunately that does not work here. I need them in a way that all dates come horizontally and C_ID, E_NAME, C_NAME, P_NAME come once only.

    Please the yellow highlighted portion on the right side in the same workbook sheet to get an idea how I want the data. I created by hand to provide the reader with an idea how I want the data. It will be hard to do it for 2023 participants, so please suggest something.

    Thanks a lott for the help
    Attached Files Attached Files
    Last edited by ajang; 11-01-2010 at 09:47 AM.

  2. #2
    Forum Contributor
    Join Date
    01-28-2010
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2003
    Posts
    157

    Re: Convert Vertical row data into Horizontal

    If the C_IDs can simply be 1,2,3,4... down one column, then VLOOKUP can return the E_NAME, C_NAME and P_NAME, while an array formula filled down rows and across columns can return the dates.
    See Sheet 2 of attached doc.
    Attached Files Attached Files

  3. #3
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Convert Vertical row data into Horizontal

    With the addition of a "key" column to create an easily indexable column, you can accomplish this without array formulas, too. Here's my take.

    In this version, the column H values work themselves out, too.
    Attached Files Attached Files
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  4. #4
    Registered User
    Join Date
    10-13-2010
    Location
    Miami, US
    MS-Off Ver
    Excel 2007
    Posts
    83

    Re: Convert Vertical row data into Horizontal

    Thank you JBeaucaire and beaunydal. It does help but I got confused somewhere in between. Will try doing it and will get back to you if have any queries.

  5. #5
    Registered User
    Join Date
    10-13-2010
    Location
    Miami, US
    MS-Off Ver
    Excel 2007
    Posts
    83

    Re: Convert Vertical row data into Horizontal

    I was looking at my data set again and I found that the client Ids are not in sequence. Can you please help how I can get that column. Everything else is same. Please the attached data set.

    I am new to the forum as well as for using excel. I was trying to make Sample data set anonymous. I am really sorry I messed it up the first time. I will take care in future before posting something.

    Thank you for the help.
    Attached Files Attached Files

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Convert Vertical row data into Horizontal

    It makes me think you didn't look at the sample I uploaded at all. Here's the exact same sheet again but with the new data from your post above pasted into columns A:E.

    The key column I added to column F and all the formulas in H:T are unchanged.
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    01-28-2010
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2003
    Posts
    157

    Re: Convert Vertical row data into Horizontal

    I might be wrong, but there might not be the need for an additional key column. The C_ID column looks unique.
    Using C_ID as the key column and adopting JBeaucaire's formulas for the C_ID values in the transposed data, this standard formula in K2, filled down and across as far as needed, seems to work...
    Please Login or Register  to view this content.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    10-13-2010
    Location
    Miami, US
    MS-Off Ver
    Excel 2007
    Posts
    83

    Re: Convert Vertical row data into Horizontal

    Got It..Thanks a toonnnn

  9. #9
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Convert Vertical row data into Horizontal

    Go team!

    If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

+ 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