Fill down column until text is found

[IAmLemondrop]

I'm working with a dataset with a strange formatting. It looks like:

Column A
Category 1
Item 1
Item 2
Item 3
Category 1 Total

Category 2
Item 4
Item 5
Item 6
Category 2 Total

Instead of utilizing different columns, all of the above in in one column (with the sub items indented with 5 spaces). I'd like to break out the Category titles and put them in the column next to their corresponding item.

So I'm looking for a macro (or a formula), that will give me:
Column A Column B
Category 1 Item 1
Category 1 Item 2
Category 2 Item 4...

Any ideas? My immediate thoughts were to create a new column to find the total row and copy it over with an =IF(ISERR(SEARCH("*total*",B5)),"",B5). But I'm stumped on how to automate bringing out the category headers, filling down until the "Total" is reached and restarting the process at the next category header.

 

[Rosen]

Here is how I would do it...

Sub AddCatagoryHeaders()
    On Error GoTo ExitSub
    Dim CatagoryName As String, oRange As Range, lRow As Long
    Dim bLooking As Boolean: bLooking = True
    lRow = 1
    Set oRange = Range("A" & lRow)
    Do
        If bLooking Then
            If Not oRange.Value = vbNullString Then
                CatagoryName = oRange.Value
                bLooking = False
            End If
        Else
            If Strings.Len(oRange.Value) < 6 Then
                bLooking = True
            ElseIf Not Strings.Left(oRange.Value, 5) = "     " Then
                bLooking = True
            Else
                oRange.Value = CatagoryName & " " & Trim(oRange.Value)
            End If
        End If
        lRow = lRow + 1
        Set oRange = Range("A" & lRow)
    Loop While True
ExitSub:
End Sub

Note, limited testing done, make sure to make a backup of the workbook before running the code.

Hope that helps!

 

[IAmLemondrop]

Wow. That was pretty awesome. I've been struggling to understand your code though. Is there a way to put this into two columns? So category 1 in column A and item 1 in column B? My category names aren't always going to be the same character length so I couldn't just do a =right(whatever) to pull it out.

Thank you so much for all of your help!

 

[Rosen]

Try this,

Sub AddCatagoryHeaders()
    On Error GoTo ExitSub
    Dim CatagoryName As String, oRange As Range, lRow As Long
    Dim bLooking As Boolean: bLooking = True
    lRow = 1
    Set oRange = Range("A" & lRow)
    Do
        If bLooking Then
            If Not oRange.Value = vbNullString Then
                CatagoryName = oRange.Value
                bLooking = False
            End If
        Else
            If Strings.Len(oRange.Value) < 6 Then
                bLooking = True
            ElseIf Not Strings.Left(oRange.Value, 5) = "     " Then
                bLooking = True
            Else
                oRange.Value = CatagoryName
                oRange.Offset(0, 1).Value = Trim(oRange.Value)
            End If
        End If
        lRow = lRow + 1
        Set oRange = Range("A" & lRow)
    Loop While True
ExitSub:
End Sub

Again, not fully tested, make sure you backup the workbook before running the code.

Hope that helps!

 

[IAmLemondrop]

Thank you! I just changed the offset in that last Else statement and it works perfectly. You have no idea how much time you've saved me.

            Else
                oRange.Offset(0, -1).Value = CategoryName
                oRange.Value = Trim(oRange.Value)
            End If