Excel - find string in multiple cells and create list

I have an Excel file with server names in column A and and errors in column D. I want to create a list of servers that contain specific text from column D.

Say I have 50 servers. In the error list I want to know how many servers have the error reading " Account Lockout Threshold" so I can identify which ones need to be remedied of this particular issue. The D column has multiple lines of text with errors. Does anyone have any ideas on how I can isolate each error and identify each server with this error?

I've attached the file for referencing.
Who is Participating?
nutschConnect With a Mentor Commented:
With the attached macro, you can convert your data into a proper list, that you can filter, pivot, etc.


Sub SplitCellsAndExtend_New()
'takes cells with inside line feeds and creates new row for each.
'reverses merge into top cell.

'turn off updates to speed up code execution
With application
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
End With

Dim strCell As String, lastRow As Long, lRowLoop As Long, j As Long, arSplit
application.ScreenUpdating = False

Const lColSplit As Long = 4
Const sFirstCell As String = "A1"
Dim sSplitOn As String
sSplitOn = Chr(10)

lastRow = Cells(Rows.Count, lColSplit).End(xlUp).Row

    For lRowLoop = lastRow To 1 Step -1
        arSplit = Split(Cells(lRowLoop, lColSplit), sSplitOn)
        If UBound(arSplit) > 0 Then
            Rows(lRowLoop + 1).Resize(UBound(arSplit) + 1).Insert
            Cells(lRowLoop, lColSplit).Resize(, UBound(arSplit) + 1).Value = arSplit
            Cells(lRowLoop, lColSplit).Resize(, UBound(arSplit) + 1).Copy
            Cells(lRowLoop + 1, lColSplit).PasteSpecial Transpose:=True
            Cells(lRowLoop, 1).Resize(, lColSplit - 1).Copy Cells(lRowLoop + 1, 1).Resize(UBound(arSplit) + 1)
        End If
        Set arSplit = Nothing
With application
    .ScreenUpdating = True
    .EnableEvents = True
    .Calculation = xlCalculationAutomatic
End With

End Sub

Open in new window

Place this formula in E1 and copy down:

=IFERROR(FIND("Account Lockout Threshold",D1),"NO")

or this one (2003 compliant)

=IF(ISERROR(FIND("Account Lockout Threshold",D1)),"NO","YES")
=FIND("Account Lockout Threshold",D1,1)
P0larb3arAuthor Commented:
Worked like a champ! Thank you Thomas.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.