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.
Don’t have an account yet? Register yourself now and be a part of our community!
