Thread: How to print a column as a list of headings (for a karaoke book)

Hey everyone,

I'm wondering how to print a spreadsheet like this:

Alice Adams, Song 1
Alice Adams, Song 2
Bob Baker, Song 1
Bob Baker, Song 2
Bob Baker, Song 3

into something that looks like this:

Alice Adams
Song 1
Song 2
Bob Baker
Song 1
Song 2
Song 3

How do I do this in Excel?

The following assumes the list is sorted and starts in column A and row 1. I've commented out the line that would delete the original column A after the seperation has taken place.

Code:

Sub moveIt()
    Dim i As Long, lRow As Long, strSing As String, strSong As String, strSingLast As String, iRow As Long
    lRow = Cells(Rows.Count, 1).End(xlUp).Row
    iRow = 1
    For i = 1 To lRow
        strSing = Trim(Left(Cells(i, 1), InStr(Cells(i, 1), ",") - 1))
        If strSing <> strSingLast Then
            Cells(iRow, 2) = strSing
            Cells(iRow, 2).Font.Bold = True

            iRow = iRow + 1
            strSingLast = strSing
        End If
        Cells(iRow, 2) = Trim(Mid(Cells(i, 1), InStr(Cells(i, 1), ",") + 1, 99))
        iRow = iRow + 1
    Next

    'Cells(1, 1).EntireColumn.Delete

End Sub