Transpose Macro (Vertical to Horizontal) for a varying Number of rows to varying number of columns.

biostatistician · Oct 24, 2014

Hi all,
New this forum. I'm not a VB expert but did some work with VB years ago back in graduate school so I'm not too familiar anymore.

I'm basically trying to take Column G, and transpose it accordingly. For example, STUDY #2 has 3 extra rows in Column G which I want to transpose to ROW K and onward.
I want to do the same for Study 3 and 4 and so on for almost 3000 STUDY #'s. But the number of rows vary. Is there a code to do this? I know it will be conditional and I need the code to realize it's finished transposing for that STUDY # once the next STUDY appears. I've posted an example of what I'm looking for below. Any help with this would be greatly appreciated!

WHAT I HAVE:

A B C D E F G H I J K L
[TABLE="width: 1280"]
<tbody>[TR]
[TD="class: xl67, width: 64"]STUDY[/TD]
[TD="class: xl67, width: 64"]Present[/TD]
[TD="class: xl67, width: 64"]Gen[/TD]
[TD="class: xl67, width: 64"]Number[/TD]
[TD="class: xl67, width: 64"]race[/TD]
[TD="class: xl67, width: 64"]short[/TD]
[TD="class: xl67, width: 64"]diag[/TD]
[TD="class: xl67, width: 64"]Ad[/TD]
[TD="class: xl67, width: 64"]Di[/TD]
[TD="class: xl69, width: 64"]Re[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl64"]1[/TD]
[TD="class: xl64"] Y[/TD]
[TD="class: xl64"]F[/TD]
[TD="class: xl65"]30[/TD]
[TD="class: xl68"]O[/TD]
[TD="class: xl64"]81200[/TD]
[TD="class: xl64"]81201[/TD]
[TD="class: xl68"]M[/TD]
[TD="class: xl68"]M[/TD]
[TD="class: xl63"]**[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"]2[/TD]
[TD="class: xl64"] Y[/TD]
[TD="class: xl64"]M[/TD]
[TD="class: xl65"]20[/TD]
[TD="class: xl68"]J[/TD]
[TD="class: xl64"]83410[/TD]
[TD="class: xl64"]83412[/TD]
[TD="class: xl68"]O[/TD]
[TD="class: xl68"]O[/TD]
[TD="class: xl63"]**[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"]30501[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl68"]E8888[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl68"]E8498[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"]3[/TD]
[TD="class: xl64"] Y[/TD]
[TD="class: xl64"]M[/TD]
[TD="class: xl65"]40[/TD]
[TD="class: xl68"]B[/TD]
[TD="class: xl64"]80230[/TD]
[TD="class: xl64"]80235[/TD]
[TD="class: xl68"]S[/TD]
[TD="class: xl68"]S[/TD]
[TD="class: xl63"]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl68"]E9688[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl68"]E8495[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"]4[/TD]
[TD="class: xl64"] Y[/TD]
[TD="class: xl64"]M[/TD]
[TD="class: xl65"]50[/TD]
[TD="class: xl68"]B[/TD]
[TD="class: xl64"]81510[/TD]
[TD="class: xl64"]81514[/TD]
[TD="class: xl68"]S[/TD]
[TD="class: xl68"]S[/TD]
[TD="class: xl63"]**[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"]8832[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl68"]E9174[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl68"]E8498[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"]71894[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"]9556[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"]5[/TD]
[TD="class: xl64"]N[/TD]
[TD="class: xl64"]F[/TD]
[TD="class: xl65"]25[/TD]
[TD="class: xl68"]M[/TD]
[TD="class: xl64"]82382[/TD]
[TD="class: xl64"]82302[/TD]
[TD="class: xl68"]S[/TD]
[TD="class: xl68"]O[/TD]
[TD="class: xl63"]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"]8082[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"]8056[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"]2851[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"]81201[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"]920[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"]85011[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl68"]E8147[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl68"]E8495[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"]4019[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl71"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"]2724[/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl72"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]WHAT I NEED:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A [/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[TD]S[/TD]
[TD]T[/TD]
[/TR]
[TR]
[TD="class: xl73, width: 64"]STUDY[/TD]
[TD="class: xl73, width: 64"]Present[/TD]
[TD="class: xl73, width: 64"]Gen[/TD]
[TD="class: xl73, width: 64"]Number[/TD]
[TD="class: xl73, width: 64"]race[/TD]
[TD="class: xl73, width: 64"]short[/TD]
[TD="class: xl73, width: 64"]diag[/TD]
[TD="class: xl73, width: 64"]Ad[/TD]
[TD="class: xl73, width: 64"]Di[/TD]
[TD="class: xl73, width: 64"]Re[/TD]
[TD="class: xl73, width: 64"][/TD]
[TD="class: xl73, width: 64"][/TD]
[TD="class: xl73, width: 64"][/TD]
[TD="class: xl73, width: 64"][/TD]
[TD="class: xl73, width: 64"][/TD]
[TD="class: xl73, width: 64"][/TD]
[TD="class: xl74"][/TD]
[TD="class: xl74"][/TD]
[TD="class: xl74"][/TD]
[TD="class: xl74"][/TD]
[/TR]
[TR]
[TD="class: xl75"]1[/TD]
[TD="class: xl75"] Y[/TD]
[TD="class: xl75"]F[/TD]
[TD="class: xl76"]30[/TD]
[TD="class: xl77"]O[/TD]
[TD="class: xl75"]81200[/TD]
[TD="class: xl75"]81201[/TD]
[TD="class: xl77"]M[/TD]
[TD="class: xl77"]M[/TD]
[TD="class: xl75"]**[/TD]
[TD="class: xl74"][/TD]
[TD="class: xl74"][/TD]
[TD="class: xl74"][/TD]
[TD="class: xl74"][/TD]
[TD="class: xl74"][/TD]
[TD="class: xl74"][/TD]
[TD="class: xl74"][/TD]
[TD="class: xl74"][/TD]
[TD="class: xl74"][/TD]
[TD="class: xl74"][/TD]
[/TR]
[TR]
[TD="class: xl75"]2[/TD]
[TD="class: xl75"] Y[/TD]
[TD="class: xl75"]M[/TD]
[TD="class: xl76"]20[/TD]
[TD="class: xl77"]J[/TD]
[TD="class: xl75"]83410[/TD]
[TD="class: xl75"]83412[/TD]
[TD="class: xl77"]O[/TD]
[TD="class: xl77"]O[/TD]
[TD="class: xl75"]**[/TD]
[TD="class: xl75"]30501[/TD]
[TD="class: xl77"]E8888[/TD]
[TD="class: xl77"]E8498[/TD]
[TD="class: xl74"][/TD]
[TD="class: xl74"][/TD]
[TD="class: xl74"][/TD]
[TD="class: xl74"][/TD]
[TD="class: xl74"][/TD]
[TD="class: xl74"][/TD]
[TD="class: xl74"][/TD]
[/TR]
[TR]
[TD="class: xl75"]3[/TD]
[TD="class: xl75"] Y[/TD]
[TD="class: xl75"]M[/TD]
[TD="class: xl76"]40[/TD]
[TD="class: xl77"]B[/TD]
[TD="class: xl75"]80230[/TD]
[TD="class: xl75"]80235[/TD]
[TD="class: xl77"]S[/TD]
[TD="class: xl77"]S[/TD]
[TD="class: xl75"]Y[/TD]
[TD="class: xl77"]E9688[/TD]
[TD="class: xl77"]E8495[/TD]
[TD="class: xl74"][/TD]
[TD="class: xl74"][/TD]
[TD="class: xl74"][/TD]
[TD="class: xl74"][/TD]
[TD="class: xl74"][/TD]
[TD="class: xl74"][/TD]
[TD="class: xl74"][/TD]
[TD="class: xl74"][/TD]
[/TR]
[TR]
[TD="class: xl75"]4[/TD]
[TD="class: xl75"] Y[/TD]
[TD="class: xl75"]M[/TD]
[TD="class: xl76"]50[/TD]
[TD="class: xl77"]B[/TD]
[TD="class: xl75"]81510[/TD]
[TD="class: xl75"]81514[/TD]
[TD="class: xl77"]S[/TD]
[TD="class: xl77"]S[/TD]
[TD="class: xl75"]**[/TD]
[TD="class: xl75"]8832[/TD]
[TD="class: xl77"]E9174[/TD]
[TD="class: xl77"]E8498[/TD]
[TD="class: xl75"]71894[/TD]
[TD="class: xl75"]9556[/TD]
[TD="class: xl74"][/TD]
[TD="class: xl74"][/TD]
[TD="class: xl74"][/TD]
[TD="class: xl74"][/TD]
[TD="class: xl74"][/TD]
[/TR]
[TR]
[TD="class: xl75"]5[/TD]
[TD="class: xl75"]N[/TD]
[TD="class: xl75"]F[/TD]
[TD="class: xl76"]25[/TD]
[TD="class: xl77"]M[/TD]
[TD="class: xl75"]82382[/TD]
[TD="class: xl75"]82302[/TD]
[TD="class: xl77"]S[/TD]
[TD="class: xl77"]O[/TD]
[TD="class: xl75"]Y[/TD]
[TD="class: xl75"]8082[/TD]
[TD="class: xl75"]8056[/TD]
[TD="class: xl75"]2851[/TD]
[TD="class: xl75"]81201[/TD]
[TD="class: xl75"]920[/TD]
[TD="class: xl75"]85011[/TD]
[TD="class: xl77"]E8147[/TD]
[TD="class: xl77"]E8495[/TD]
[TD="class: xl75"]4019[/TD]
[TD="class: xl75"]2724[/TD]
[/TR]
</tbody>[/TABLE]

hiker95 · Oct 25, 2014

biostatistician,

If my latest screenshot of the raw data worksheet was correct then the macro should have worked.

I would be happy to try my macro on your actual raw data.

You can upload your workbook to Box Net,

sensitive data changed

mark the workbook for sharing

and provide us with a link to your workbook.

biostatistician · Oct 25, 2014

Rick Rothstein said:

Here is another macro that you can try...

Code:

Sub TransposeDiagData()
  Dim Blanks As Range, Ar As Range
  On Error GoTo NoBlanks
  Set Blanks = Range("B1:B" & Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlBlanks)
  For Each Ar In Intersect(Blanks.EntireRow, Columns("M")).Areas
    Intersect(Ar(1).Offset(-1).EntireRow, Columns("Q")).Resize(, Ar.Count) = Application.Transpose(Ar)
  Next
  Blanks.EntireRow.Delete
NoBlanks:
End Sub

Hi Rick,

I tried your code, and sadly it doesn't seem to work. I don't receive any errors, but it doesn't seem to be changing anything. Do you have any idea how I can tweak your code to make it work? Thanks!

Rick Rothstein · Oct 25, 2014

biostatistician said:
I tried your code, and sadly it doesn't seem to work. I don't receive any errors, but it doesn't seem to be changing anything. Do you have any idea how I can tweak your code to make it work?

The only way my code would not work is if the "blanks" in Column B were not truly blank. This can happen in two ways (that I can think of off the top of my head)... you have formulas in the cells of Column B (where the blanks are really "" outputted by the formula) or you have an "invisible" character in those "blank" cells (such as space character or, if you copied the data from another source, possible a non-breaking space whose ASCII code is 160). So, first question... do you have formulas in the cells of Column B? If not, what about the contents of the "blank" cells... put his formula in a cell and see if it displays 0 (meaning blanks are really blank) or some number greater than zero (meaning your blanks are not really blank).

=COUNTIF(B:B," ")+COUNTIF(B:B,CHAR(160))

biostatistician · Oct 25, 2014

Rick Rothstein said:
The only way my code would not work is if the "blanks" in Column B were not truly blank. This can happen in two ways (that I can think of off the top of my head)... you have formulas in the cells of Column B (where the blanks are really "" outputted by the formula) or you have an "invisible" character in those "blank" cells (such as space character or, if you copied the data from another source, possible a non-breaking space whose ASCII code is 160). So, first question... do you have formulas in the cells of Column B? If not, what about the contents of the "blank" cells... put his formula in a cell and see if it displays 0 (meaning blanks are really blank) or some number greater than zero (meaning your blanks are not really blank).

=COUNTIF(B:B," ")+COUNTIF(B:B,CHAR(160))

Hi Rick,
Thanks again for your help. I got your code to work after some manipulating. It might have been do with the formatting of the file, So I exported to CSV to strip it of all the other formatting and then back XLSM. However, I noticed that the example posted before had one small error in it which is Column A was all numbers that continued through the data set..that was originally just the cell numbers so column A with numbers shouldn't be there but in place it should be the corresponding Names, so Column A has varying rows and not a continuous set of rows filled. I have the wrong data and correct format below. If we go with the second example. How can I tweak the code accordingly? I tried to do it but then it doesn't run.

COLUMN A is Incorrect Here. It' shouldn't be numbers but names accordingly. THe following table is correct.

Excel 2007

A B C D E F G H I J K L M N O P Q
STU
DY Pres
ent Gen Num
ber race sou
rce pri
ority date d
date los short diag Ad Di Re
Y F O M M **
Y M J O O **
E8888
E8498
Y M B S S Y
E9688
E8495
Y M B S S **
E9174
E8498
N F M S O Y
E8147
E8495

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]30[/TD]

[TD="align: right"]12[/TD]
[TD="align: right"]234[/TD]
[TD="align: right"]324[/TD]
[TD="align: right"]5234[/TD]
[TD="align: right"]213[/TD]
[TD="align: right"]81200[/TD]
[TD="align: right"]81201[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"]20[/TD]

[TD="align: right"]31[/TD]
[TD="align: right"]532[/TD]
[TD="align: right"]325[/TD]
[TD="align: right"]235[/TD]
[TD="align: right"]325[/TD]
[TD="align: right"]83410[/TD]
[TD="align: right"]83412[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]30501[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"]40[/TD]

[TD="align: right"]23[/TD]
[TD="align: right"]523[/TD]
[TD="align: right"]532[/TD]
[TD="align: right"]213[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]80230[/TD]
[TD="align: right"]80235[/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]4[/TD]

[TD="align: right"]50[/TD]

[TD="align: right"]53[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]213[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]231[/TD]
[TD="align: right"]81510[/TD]
[TD="align: right"]81514[/TD]

[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]8832[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]71894[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]9556[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"]25[/TD]

[TD="align: right"]234[/TD]
[TD="align: right"]645[/TD]
[TD="align: right"]56345[/TD]
[TD="align: right"]342[/TD]
[TD="align: right"]234[/TD]
[TD="align: right"]82382[/TD]
[TD="align: right"]82302[/TD]

[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]8082[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]8056[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2851[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]81201[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]920[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]85011[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]26[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2724[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]27[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet

The following is correct

Excel 2007

A B C D E F G H I J K L M N O P Q
STU
DY Pres
ent Gen Num
ber race sou
rce pri
ority date d
date los short diag Ad Di Re
Y F O M M **
Y M B S S Y
E9688
E8495
N F M S O Y
E8147
E8495

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]Name[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]John Smith[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]30[/TD]

[TD="align: right"]12[/TD]
[TD="align: right"]234[/TD]
[TD="align: right"]324[/TD]
[TD="align: right"]5234[/TD]
[TD="align: right"]213[/TD]
[TD="align: right"]81200[/TD]
[TD="align: right"]81201[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]
John Smith

<tbody>
</tbody>
[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"]40[/TD]

[TD="align: right"]23[/TD]
[TD="align: right"]523[/TD]
[TD="align: right"]532[/TD]
[TD="align: right"]213[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]80230[/TD]
[TD="align: right"]80235[/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]
John Smith

<tbody>
</tbody>
[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"]25[/TD]

[TD="align: right"]234[/TD]
[TD="align: right"]645[/TD]
[TD="align: right"]56345[/TD]
[TD="align: right"]342[/TD]
[TD="align: right"]234[/TD]
[TD="align: right"]82382[/TD]
[TD="align: right"]82302[/TD]

[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]8082[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]8056[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2851[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]81201[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]920[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]85011[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]26[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2724[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]27[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet

biostatistician · Oct 25, 2014

Hi Hiker95,

Thanks again for all your help. So I think in the last example, in error Column A turned into a continuous row numbers when those were actually names which were varying too--thought this might be the reason why it didn't work. I tried Rick's macro which worked after I stripped all the formatting from the database. For some reason your macro kept crashing the excel file and made the computer go completely blank

. I tried the reformatting it also and it still happened. Check the reply I put for Rick's code.

Rick Rothstein · Oct 25, 2014

If I understand what the problem might have been originally, and given the method you used to "fix" it, I think the following code should work on your original file directly without you having to export it at all... give this macro a try and let us know if it worked or not.

Code:

Sub TransposeDiagData()
  Dim LastRow As Long, Blanks As Range, Ar As Range
  LastRow = Cells(Rows.Count, "M").End(xlUp).Row
  Range("A1:A" & LastRow).Value = Range("A1:A" & LastRow).Value
  On Error GoTo NoBlanks
  Set Blanks = Range("A1:A" & LastRow).SpecialCells(xlBlanks)
  For Each Ar In Intersect(Blanks.EntireRow, Columns("M")).Areas
    Intersect(Ar(1).Offset(-1).EntireRow, Columns("Q")).Resize(, Ar.Count) = Application.Transpose(Ar)
  Next
  Blanks.EntireRow.Delete
NoBlanks:
End Sub

hiker95 · Oct 25, 2014

biostatistician,

I copied your text display, but, when I pasted into a worksheet it seemed different/not correct?????

Is the following screenshot correct in reference to rows, and, columns?

Excel 2007

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

1

Name

STU DY

Pres ent

Gen

Num ber

race

sou rce

pri ority

date

d date

los

short

diag

Ad

Di

Re

2

John Smith

1

Y

F

30

O

12

234

324

5234

213

81200

81201

M

**

3

John Smith

3

Y

M

40

B

23

523

532

213

123

80230

80235

S

Y

4

E9688

5

E8495

6

John Smith

5

N

F

25

M

234

645

56345

342

234

82382

82302

S

O

Y

7

8082

8

8056

9

2851

10

81201

11

920

12

85011

13

E8147

14

E8495

15

4019

16

2724

17

Sheet1

biostatistician · Oct 25, 2014

Rick Rothstein said:
If I understand what the problem might have been originally, and given the method you used to "fix" it, I think the following code should work on your original file directly without you having to export it at all... give this macro a try and let us know if it worked or not.

Code:

Sub TransposeDiagData() Dim LastRow As Long, Blanks As Range, Ar As Range LastRow = Cells(Rows.Count, "M").End(xlUp).Row Range("A1:A" & LastRow).Value = Range("A1:A" & LastRow).Value On Error GoTo NoBlanks Set Blanks = Range("A1:A" & LastRow).SpecialCells(xlBlanks) For Each Ar In Intersect(Blanks.EntireRow, Columns("M")).Areas Intersect(Ar(1).Offset(-1).EntireRow, Columns("Q")).Resize(, Ar.Count) = Application.Transpose(Ar) Next Blanks.EntireRow.Delete NoBlanks: End Sub

OH MY GOD. That was bloody BRILLIANT!
Thank you thank you thank you so much!
The code works PERFECTLY!
Thank you so much Rick, and thank you Hiker95 and MikeG for helping me with this! I wish I realized I could've used macros earlier for this type of excel work. It didn't come to my mind until very recently!

Again, THANK YOU SO MUCH! I've been struggling for a week now!! I can finally get sleep!

biostatistician · Oct 25, 2014

Thank you Hiker95! Yes that's the correct display. Rick was able to provide a code that finally worked with the original formatting of the file. I tried stripping all the formatting into a CSV and then re-exporting as XLSM which seemed to help. But regardless thank you so much for your efforts to try and help me figure this out. This has saved me so many hours of countless copy/pasting. It became almost mind-numbing.

hiker95 · Oct 25, 2014

biostatistician,

Yes that's the correct display.

Then my ReorgData_V2 will work correctly.

Please give it a try, and, let me know your results.

Here are my results:

Excel 2007

A

B

C

D

E

F

G

H

I

J

K

L

M

1

Name

STU DY

Pres ent

Gen

Num ber

race

sou rce

pri ority

date

d date

los

short

diag

2

John Smith

1

Y

F

30

O

12

234

324

5234

213

81200

81201

3

John Smith

3

Y

M

40

B

23

523

532

213

123

80230

80235

4

John Smith

5

N

F

25

M

234

645

56345

342

234

82382

82302

5

Sheet1

Excel 2007

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

1

short

diag

Ad

Di

Re

2

81200

81201

M

**

3

80230

80235

S

Y

E9688

E8495

4

82382

82302

S

O

Y

8082

8056

2851

81201

920

85011

E8147

E8495

4019

2724

5

Sheet1

Transpose Macro (Vertical to Horizontal) for a varying Number of rows to varying number of columns.

biostatistician

New Member

hiker95

Well-known Member

Excel Facts

biostatistician

New Member

Rick Rothstein

MrExcel MVP

biostatistician

New Member

biostatistician

New Member

Rick Rothstein

MrExcel MVP

hiker95

Well-known Member

biostatistician

New Member

biostatistician

New Member

hiker95

Well-known Member

Similar threads

Forum statistics

Share this page

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
STU DY	Pres ent	Gen	Num ber	race	sou rce	pri ority	date	d date	los	short	diag	Ad	Di	Re
Y	F	O	M	M	**
Y	M	J	O	O	**

E8888
E8498
Y	M	B	S	S	Y
E9688
E8495
Y	M	B	S	S	**

E9174
E8498


N	F	M	S	O	Y






E8147
E8495

Transpose Macro (Vertical to Horizontal) for a varying Number of rows to varying number of columns.

New Member

Well-known Member

Excel Facts

New Member

MrExcel MVP

New Member

New Member

MrExcel MVP

Well-known Member

New Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock