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

[biostatistician]

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

STUDY

Present

Gen

Number

race

short

diag

Ad

Di

Re

1

Y

F

30

O

81200

81201

M

M

**

2

Y

M

20

J

83410

83412

O

O

**

30501

E8888

E8498

3

Y

M

40

B

80230

80235

S

S

Y

E9688

E8495

4

Y

M

50

B

81510

81514

S

S

**

8832

E9174

E8498

71894

9556

5

N

F

25

M

82382

82302

S

O

Y

8082

8056

2851

81201

920

85011

E8147

E8495

4019

2724

WHAT I NEED:

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

STUDY

Present

Gen

Number

race

short

diag

Ad

Di

Re

1

Y

F

30

O

81200

81201

M

M

**

2

Y

M

20

J

83410

83412

O

O

**

30501

E8888

E8498

3

Y

M

40

B

80230

80235

S

S

Y

E9688

E8495

4

Y

M

50

B

81510

81514

S

S

**

8832

E9174

E8498

71894

9556

5

N

F

25

M

82382

82302

S

O

Y

8082

8056

2851

81201

920

85011

E8147

E8495

4019

2724

<tbody>
</tbody>

 

[hiker95]

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]

Here is another macro that you can try...

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]

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]

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
1	1	STU DY	Pres ent	Gen	Num ber	race	sou rce	pri ority	date	d date	los	short	diag	Ad	Di	Re
2	2	1	Y	F	30	O	12	234	324	5234	213	81200	81201	M	M	**
3	3	2	Y	M	20	J	31	532	325	235	325	83410	83412	O	O	**
4	4												30501
5	5												E8888
6	6												E8498
7	7	3	Y	M	40	B	23	523	532	213	123	80230	80235	S	S	Y
8	8												E9688
9	9												E8495
10	10	4	Y	M	50	B	53	12	213	35	231	81510	81514	S	S	**
11	11												8832
12	12												E9174
13	13												E8498
14	14												71894
15	15												9556
16	16	5	N	F	25	M	234	645	56345	342	234	82382	82302	S	O	Y
17	17												8082
18	18												8056
19	19												2851
20	20												81201
21	21												920
22	22												85011
23	23												E8147
24	24												E8495
25	25												4019
26	26												2724
27

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</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
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	M	**
3	John Smith <tbody> </tbody>	3	Y	M	40	B	23	523	532	213	123	80230	80235	S	S	Y
8													E9688
9													E8495
10	John Smith <tbody> </tbody>	5	N	F	25	M	234	645	56345	342	234	82382	82302	S	O	Y
17													8082
18													8056
19													2851
20													81201
21													920
22													85011
23													E8147
24													E8495
25													4019
26													2724
27

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet​

 

[biostatistician]

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]

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.

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]

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	M	**
3	John Smith	3	Y	M	40	B	23	523	532	213	123	80230	80235	S	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]

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.

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]

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]

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	M	**
3	80230	80235	S	S	Y	E9688	E8495
4	82382	82302	S	O	Y	8082	8056	2851	81201	920	85011	E8147	E8495	4019	2724
5
Sheet1