On Feb 6, 10:05*pm, "Rick Rothstein"
wrote:
Each has cell in 16 characters.
A 'group' has the same 11 leftmost characters.

Here's what I'm looking for:

141240009-1 2006 *141240009-1 2008 *141240009-1 2009
141361014-9 2009
* * <<<snip

Give this macro a try....

Sub TransposeGroups()
* Dim A As Range, D As Range, StartCell As Range, LastCell As Range
* Dim Index As Long, Data() As String
* Const DataCol As String = "A"
* Const StartRow As Long = 1
* Set StartCell = Cells(StartRow, DataCol)
* Set LastCell = Cells(Rows.Count, DataCol).End(xlUp)
* Set D = Range(StartCell, LastCell).SpecialCells(xlCellTypeConstants)
* ReDim Data(1 To D.Count)
* For Each A In D.Areas
* * Index = Index + 1
* * If A.Count = 1 Then
* * * Data(Index) = A
* * Else
* * * Data(Index) = Join(WorksheetFunction.Transpose(A), "|")
* * End If
* Next
* Application.ScreenUpdating = False
* Columns(DataCol).Clear
* Range(StartCell, LastCell).Resize(UBound(Data)).Value = _
* * * * * * * * * * * WorksheetFunction.Transpose(Data)
* Range(StartCell, LastCell).TextToColumns StartCell, _
* * * * * * * * * *xlDelimited, Tab:=False, Space:=False, _
* * * * * * * * * *Other:=True, OtherChar:="|"
* Application.ScreenUpdating = True
End Sub

Rick Rothstein (MVP - Excel)

Run-time Error '1004'
Unable to get the Transpose property of the WorksheetFunction class