Excel - find string in multiple cells and create list

Posted on 2012-09-05
Last Modified: 2012-09-05
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.
Question by:P0larb3ar
    LVL 24

    Expert Comment

    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")
    LVL 21

    Expert Comment

    =FIND("Account Lockout Threshold",D1,1)
    LVL 39

    Accepted Solution

    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


    Author Closing Comment

    Worked like a champ! Thank you Thomas.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now