Transpose data from column A to column B by numbers with colon

[gailb]

As you can see, there is a Before tab and an After tab. In the Before tab, I would like to take all of the cells with numbers (or references) and place them as transposed starting in column B. You can see in the After tab, all of the cells in bold are the groupings, while there are sub grouping below them until the next grouping. If everything only had one reference this would be easy for me, but as you can see in row 17 and 18, there are two so that's why I'm looking to transpose. I realize some of these my not get placed in the right columns, but this should be easy for me to fix after the fact.

[HansV]

Here is a macro. You'll have to do a lot of work to get it right.

Code: Select all

Sub Transpose()
    Dim r As Long
    Dim r0 As Long
    Dim c As Long
    Application.ScreenUpdating = False
    With Cells(1, 1).Resize(1, 7)
        .Font.Bold = True
        .Font.Color = vbWhite
        .Interior.Color = 9851952
        .HorizontalAlignment = xlHAlignCenter
    End With
    r = 2
    Do
        If Cells(r, 1).Font.Bold Then
            Cells(r, 1).Resize(1, 7).Interior.Color = 14277081
            r = r + 1
        ElseIf IsNumeric(Left(Cells(r, 1), 1)) Then
            c = c + 1
            Cells(r0, c).Value = "'" & Cells(r, 1).Value
            Cells(r, 1).EntireRow.Delete
        Else
            r0 = r
            c = 1
            r = r + 1
        End If
        DoEvents
    Loop Until Cells(r, 1).Value = ""
    With ActiveSheet.UsedRange
        .Borders.LineStyle = xlContinuous
        .Offset(1).HorizontalAlignment = xlHAlignLeft
        .EntireColumn.AutoFit
    End With
    Application.ScreenUpdating = True
End Sub

[gailb]

This appears to have done it perfectly. Yes, a few things to clean-up, but it sure cuts down in the tedious hand jamming. Thank you much.