Transposing

[gary]

COL A has 165,000 cells that looks like this:

CONTROL-NBR 1
DATA1
DATA2
DATA3
CONTROL-NBR 2
DATA1
DATA2
DATA3
DATA4
DATA5
DATA6
CONTROL-NBR 3
DATA1
CONTROL-NBR 4
DATA1
DATA2
DATA3
DATA4
DATA5

How can I transpose the "DATA" cells for each "CONTROL-NBR" onto the
same row as the "CONTROL-NBR" so the result looks like this::

CONTROL-NBR-1 DATA1 DATA2 DATA3



CONTROL-NBR-2 DATA1 DATA2 DATA3 DATA4 DATA5 DATA6






CONTROL-NBR-3 DATA1

CONTROL-NBR-4 DATA1 DATA2 DATA3 DATA4 DATA5






(Note: The number of "DATA" cells for each "CONTROL-NBR" varies from 1
to 30).

 

[StumpedAgain]

Do the CONTROL-NBR's start with "CONTROL"?

 

[StumpedAgain]

If CONTROL is at the begining of the data sets, then the following macro
should reorganize your data properly. I tested it on your example data set
and it works. Hope this helps!

Option Explicit
Sub ColToRows()

Dim curselection As Range

Set curselection = Range("A1") 'or wherever you start

Do
If curselection.Offset(0, 1) = "" Then
If Not curselection.Offset(1, 0).Value Like "CONTROL*" Then
curselection.Offset(1, 0).Cut Destination:=curselection.Offset(0, 1)
curselection.Offset(1, 0).EntireRow.Delete
End If
End If
datafound:
If Not curselection.Offset(1, 0).Value Like "CONTROL*" Then
curselection.Offset(1, 0).Cut
Destination:=curselection.End(xlToRight).Offset(0, 1)
curselection.Offset(1, 0).EntireRow.Delete
If curselection.Offset(1, 0) = "" Then Exit Do
GoTo datafound
End If
Set curselection = curselection.Offset(1, 0)
Loop

End Sub