Adding Category Rows to Excel Spreadsheet

Question

I have data that has a category column, and I need to add a row above with each category name, and would like to do this automatically with either a formula, pivot table, or macro. 

I need to:

• Add a line above each new category with the name of that category
• Delete the rest of the cells in column A for that category
• Maintain the rest of my data in Columns B, C, and D

Example Original data

Example Finished data

I have figured out how to add the category labels with a pivot table, but it adds extra data since I don't need any totals. 

The amount of lines for the category varies, so I can't add new lines every 2 rows, for example. 

Any help is appreciated!

Answer 1

Here is a macro:

Sub Transform()

    Dim r As Long

    Dim m As Long

    Application.ScreenUpdating = False

    m = Range("A" & Rows.Count).End(xlUp).Row

    For r = m - 1 To 1 Step -1

        If Range("A" & r + 1).Value = Range("A" & r).Value Then

            Range("A" & r + 1).ClearContents

        Else

            Range("A" & r + 1).EntireRow.Insert

            Range("A" & r + 1).Value = Range("A" & r + 2).Value

            Range("A" & r + 2).ClearContents

        End If

    Next r

    Application.ScreenUpdating = True

End Sub

Answer 2

Thanks so much! This worked perfectly. 

Here is a macro:

Sub Transform()

    Dim r As Long

    Dim m As Long

    Application.ScreenUpdating = False

    m = Range("A" & Rows.Count).End(xlUp).Row

    For r = m - 1 To 1 Step -1

        If Range("A" & r + 1).Value = Range("A" & r).Value Then

            Range("A" & r + 1).ClearContents

        Else

            Range("A" & r + 1).EntireRow.Insert

            Range("A" & r + 1).Value = Range("A" & r + 2).Value

            Range("A" & r + 2).ClearContents

        End If

    Next r

    Application.ScreenUpdating = True

End Sub