+ Reply to Thread
Results 1 to 15 of 15

Selective Transpose Multiple Rows of Data to Columns

  1. #1
    Registered User
    Join Date
    01-20-2013
    Location
    Denver, Colorado
    MS-Off Ver
    2011 (mac)
    Posts
    48

    Selective Transpose Multiple Rows of Data to Columns

    Hi forum,

    I've been racking my brain for a few days now trying to figure out how to selectively transpose a row of dates to columns. I'm not sure exactly how to explain this, so below is an example of what the data look like entered into the spreadsheet:

    study ID provider visit 1 visit 2 visit 3 visit 4 visit 5
    10001 Test Name 7/21/13 7/25/13 8/1/13 8/6/13 8/17/13
    10002 Test Name 7/24/13 8/10/13 8/12/13 8/15/13 9/1/13

    I'd like to extract the data into a new table on another worksheet that looks like this:
    Date provider study id visit #
    7/21/13 Test Name 10001 visit 1
    7/25/13 Test Name 10001 visit 2
    8/1/13 Test Name 10001 visit 3
    7/24/13 Test Name 10002 visit 1
    8/10/13 Test Name 10002 visit 2
    8/12/13 Test Name 10002 visit 3

    This is just a quick example, but basically it would continue through all possible visit dates for the first study ID, then move to the next row of data (i.e. the next study ID) and extract the data from the row and transpose it in the appropriate columns moving down...does this make any sense? If so, any suggestions on how to actually make this happen?

    Thanks!

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Selective Transpose Multiple Rows of Data to Columns

    http://www.excelforum.com/the-water-...-question.html
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Registered User
    Join Date
    01-20-2013
    Location
    Denver, Colorado
    MS-Off Ver
    2011 (mac)
    Posts
    48

    Re: Selective Transpose Multiple Rows of Data to Columns

    Thanks for the reply, Oeldere!

    Attached is a sample workbook.

    DateRangeTransposeExample.xlsx

    Based on this workbook, data is entered onto the "Source" worksheet manually. I'd then like the data to be automatically transposed on to the "Calendar Items" worksheet, such that the "details" (i.e. column: $D) lists the corresponding header from which the date listed in "date" field (column: $A or worksheet "Calendar Items") came from. Please let me know if you can think of any ways to make this work! Thanks!!

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Selective Transpose Multiple Rows of Data to Columns

    See if this is what you're up to.

    I used the macro below to get the result.

    See the attached file.

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    01-20-2013
    Location
    Denver, Colorado
    MS-Off Ver
    2011 (mac)
    Posts
    48

    Re: Selective Transpose Multiple Rows of Data to Columns

    Hi oeldere,

    Thanks for the help! Great code & it works fine in the workbook you attached, but when I change the code a bit and insert it into a larger project...I'm getting hung up at the following line of code:

    Sheets("ScheduleDetail").Range("A" & rsht2).Value = Sheets("HSmSchedule ").Cells(i, col).Value

    All I've really done is change the worksheet names, and reorder the output a bit (which worked fine when I altered the code in the .xlsm you attached). I'm not sure why it is giving me "Run-time error '9'. Subscript out of range." Below is the slightly altered code I'm using. Please let me know if you have any additional advice! Thanks for all your help already!!

    Please Login or Register  to view this content.

  6. #6
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: Selective Transpose Multiple Rows of Data to Columns

    Another option

  7. #7
    Registered User
    Join Date
    01-20-2013
    Location
    Denver, Colorado
    MS-Off Ver
    2011 (mac)
    Posts
    48

    Re: Selective Transpose Multiple Rows of Data to Columns

    Hi AB33,

    Thanks for the reply! I'm getting a mixed bag of Providers & Visit dates in the third column ("WCC", column c) when I run your macro... any ideas why this might be? Thanks!

  8. #8
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: Selective Transpose Multiple Rows of Data to Columns

    Mile,
    To be frank, I did not see the output data when I wrote the code. Could you please include your desired output with the attached so that I can amend the code. So you have your input data sheet and a separate sheet an output(Result) sheet.

  9. #9
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Selective Transpose Multiple Rows of Data to Columns

    Your code got an space behind the name

    Please Login or Register  to view this content.
    Try the code below

    Please Login or Register  to view this content.
    Please reply if this solves your problem.

  10. #10
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Selective Transpose Multiple Rows of Data to Columns

    @loveleo

    Please always refer to whom your responding.

    Your respond to Milehigh_PhD, AB33 or Oeldere?

  11. #11
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: Selective Transpose Multiple Rows of Data to Columns

    Oeldere,
    I think my code is probably wrong as I could not see the sheet names you are using in your code.

    Please Login or Register  to view this content.

  12. #12
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,483

    Re: Selective Transpose Multiple Rows of Data to Columns

    Hi,

    I have code for this type of "transpose" problem that I've called CrossTab To Table. I've generalized it to deal with a fixed number of columns on the left and variable columns to the right of it. The last question I answered using the code is attached.

    Find also your file with the data copied to a new sheet (you need to copy VALUES ONLY) to the new sheet and run the macro on it. My macro is included in the attached. See the result on Sheet5 of the attached. Is that what you need?
    Last edited by MarvinP; 07-22-2013 at 09:06 AM.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  13. #13
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Selective Transpose Multiple Rows of Data to Columns

    The OP change my code in #4 (e.g. the sheetname) and got an error on that.

    I gave a suggestion (without seeing his/her data) what coulde be the problem (#10).

    I wait till the OP will reply on this treat.

  14. #14
    Registered User
    Join Date
    01-20-2013
    Location
    Denver, Colorado
    MS-Off Ver
    2011 (mac)
    Posts
    48

    Re: Selective Transpose Multiple Rows of Data to Columns

    Hi oeldere...or should I call you eagle eyes! Your fix in post #10 fixed the issue. It was really strange, it didn't look like there was an extra space but I deleted the e" and retyped them...now it works!!

    Thanks for all your help!

  15. #15
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Selective Transpose Multiple Rows of Data to Columns

    Thanks for the reply.

    Glad i could help.

    You can add rep(utation)point to the people who helped you by clicking on the star.

    If you got time, you also could respond on the solution of MarvinP.

+ 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: 4
    Last Post: 03-08-2013, 09:49 AM
  2. [SOLVED] How to transpose non-uniform data in multiple columns into multiple rows?
    By alexxgalaxy in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-03-2013, 08:40 PM
  3. [SOLVED] Macro to transpose data from two columns into multiple rows
    By Briansva92 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-27-2012, 07:55 PM
  4. transpose multiple rows into two columns
    By thane in forum Excel General
    Replies: 2
    Last Post: 09-14-2010, 12:48 AM
  5. transpose multiple rows and columns
    By prawer in forum Excel General
    Replies: 2
    Last Post: 06-03-2009, 04:20 PM

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