finding text in cells

  • Hi All,


    I have a spreadsheet where each cell in the range A1:A20 has a list of concatenated names, e.g. Jack, Mary, Jim, Stuart, Tracy etc. Is it possible to highlight all of the cells where the same two names appear e.g. Jack and Stuart or Jack and Jim. If not highlighting, is it possible to count how many times the two names, which can be typed in any free cell appear in the same cells in the range A1:A20?


    Thanks for any help in advance.


    Regards,
    Bill

  • Hi Ranger,


    try this if a macro is OK with you



    Sub CountHighlightNames()


    Dim Name As String, Rw As Integer, Count As Integer


    Count = 0


    Name = InputBox("Name to search for")


    For Rw = 1 To 20
    Cells(Rw, 1).Interior.ColorIndex = xlNone
    If InStr(1, Cells(Rw, 1), Name, 1) Then
    Count = Count + 1
    Cells(Rw, 1).Interior.ColorIndex = 6
    End If
    Next


    MsgBox "Found " & Name & " " & Count & " times"


    End Sub




    The search is not case senesitive.


    ie. jack will still find Jack


    Cells should become yellow if the name is found!

  • One Way using a formula;
    Enter formula in B1, notice you will have to include the comma,


    =COUNTIF(A1:A20,"Jack,")


    Using the cell reference;
    Where A1 is the name you want to count
    =COUNTIF(A1:A20,A1)


    You could use Conditional Formatting for your Highlighting options. Go To Format>Conditional Formatting and you can enter up to 3 conditions for what you want.


    If you want to trim the comma out of the names and place in a seperate column you could use this


    =TRIM(RIGHT(D2,LEN(D2)-FIND(",",D2))) & " " & TRIM(LEFT(D2,FIND(",",D2,1)-1))


    If a name was in Cell D2 like this Schmoe, Joe, you would put this formula in cell D3 and it would return the name Joe Schmoe.