Selective Transpose Multiple Rows of Data to Columns

[MileHigh_PhD]

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!

[oeldere]

http://www.excelforum.com/the-water-...-question.html

[MileHigh_PhD]

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!!

[oeldere]

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.

[MileHigh_PhD]

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.

[AB33]

Another option

[MileHigh_PhD]

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!

[AB33]

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.

[oeldere]

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.

[oeldere]

@loveleo

Please always refer to whom your responding.

Your respond to Milehigh_PhD, AB33 or Oeldere?

[AB33]

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.

[MarvinP]

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?

[oeldere]

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.

[MileHigh_PhD]

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!

[oeldere]

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.