Transpose Data from rows to columns

[noclue88]

Hello,<o></o>
I’m trying to transpose the following information in Excel 2010:<o></o>
[TABLE="class: MsoTableGrid"]
<TBODY>[TR]
[TD="width: 607, bgcolor: transparent"]UAMS Family Medical Center Fort Smith-AHEC WEST<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 607, bgcolor: transparent"]612 South 12th Street<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 607, bgcolor: transparent"]Fort Smith, AR 72901<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 607, bgcolor: transparent"]UAMS Internal Medicine North Clinic<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 607, bgcolor: transparent"]4301 W. Markham, Slot 641<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 607, bgcolor: transparent"]Little Rock, AR 72205<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 607, bgcolor: transparent"]UAMS Internal Medicine South<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 607, bgcolor: transparent"]4301 W. Markham, STE 547-15<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 607, bgcolor: transparent"]Little Rock, AR 72205-7199<o></o>
[/TD]
[/TR]
</TBODY>[/TABLE]
<o> </o>
To look like this: <o></o>
[TABLE="class: MsoTableGrid"]
<TBODY>[TR]
[TD="width: 222, bgcolor: transparent"]UAMS Family Medical Center Fort Smith-AHEC WEST<o></o>
[/TD]
[TD="width: 186, bgcolor: transparent"]612 South 12th Street<o></o>
[/TD]
[TD="width: 162, bgcolor: transparent"]Fort Smith, AR 72901<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 222, bgcolor: transparent"]UAMS Internal Medicine North Clinic<o></o>
[/TD]
[TD="width: 186, bgcolor: transparent"]4301 W. Markham, Slot 641<o></o>
[/TD]
[TD="width: 162, bgcolor: transparent"]Little Rock, AR 72205<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 222, bgcolor: transparent"]UAMS Internal Medicine South<o></o>
[/TD]
[TD="width: 186, bgcolor: transparent"]4301 W. Markham, STE 547-15<o></o>
[/TD]
[TD="width: 162, bgcolor: transparent"]Little Rock, AR 72205-7199<o></o>
[/TD]
[/TR]
</TBODY>[/TABLE]
<o> </o>
The only catch is that there are 21051 rows of this. <o></o>
So to be clear, the second and third rows need to be transposed in the first row and deleted (if possible).<o></o>
The fields are “Facility”, “Address”, “Zip”.<o></o>
Some VBA or macro would be ideal here, however, I just don’t know how to do it.<o></o>
Thanks for your help! Let me know if I need to clarify.<o></o>

 

[Rick Rothstein]

This macro should work just about as fast as is possible to process all that data...

Sub TransposeData()
  Dim X As Long, LastRow As Long, DataIn As Variant, DataOut As Variant
  Const StartRow As Long = 1
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  DataIn = Cells(StartRow, "A").Resize(LastRow - StartRow + 1)
  ReDim DataOut(1 To UBound(DataIn) \ 3, 1 To 3)
  For X = StartRow To LastRow Step 3
    DataOut(1 + X \ 3, 1) = DataIn(X, 1)
    DataOut(1 + X \ 3, 2) = DataIn(X + 1, 1)
    DataOut(1 + X \ 3, 3) = DataIn(X + 2, 1)
  Next
  Columns("A").Clear
  Cells(StartRow, "A").Resize(UBound(DataIn) \ 3, 3) = DataOut
End Sub

The only thing you might have to change is the value assigned to the StartRow constant (the Const statement)... that needs to be the row number for your first piece of data.

 

[noclue88]

Thanks Rick! It worked out great!