Find common name over three columns

[Jeff P]

Hello, I'm In need of a script...


I have situation whereby i need to create a marker to identify specific names within a range.
the range spans C17:E292 ("Names_Range")



As an example, every row that the name "Bob" appears,(within the range) to place an "X" in the corresponding row under column "H"

C......D.....E....F....G....H

Bob...Mary..................X
...... Joe

Ted...Bob...................X
Fred..........Bob...........X



Thanks for any help!

Jeff

 

[Aladin Akyurek]

Let H16 house Bob, the criterion name.

In H17 enter and copy down:

=IF(ISNUMBER(MATCH($H$16,C17:E17,0)),"X","")

Note. This formula is faster than one with the COUNTIF function.

 

[Jeff P]

Thanks for the quick reply, Aladin

I should have mentioned that the requested script would need to be initiated via a drop down menu, in which I would select the appropriate name. ( I have the drop down list setup already.. but now I'm looking to find the actual script it calls)

Thanks for the help!

Jeff

 

[p45cal]

Change the $H$16 in the formula to the address of the cell containing the dropdown, retaining the $ characters.

 

[Jeff P]

Thanks for the idea.

The method of copying the formula into the entire H column would work, however i may run into a problem as column H may already contain information.

any ideas?

thanks

jeff

 

[p45cal]

1. Use another, free, column.
2. What's going to use the marker? The computer(eg. for a filter) or a human (only a visual marker is required? If the latter you could try conditional formatting using a very similar formula to Aladin's

 

[Jeff P]

Thanks, p45cal

Column H is kind of a communal area.

I have scripts calling other scripts placing data from numerous sources within column H.. in which this data is tabulated and calls another script..
I try to avoid using conditional formatting because of the lack of control (sometimes i need to modify things)

The idea of using another column is fine for the initial formula, but I would then need a way to reference this back to column H ( which may already have information there)

What about something like this, below (a partial extraction from another script being used)
Could something like this be modified to create an X marker within column H?



Sub Sort_Names_Bob()
Range("NamesSort") = "=IF(OR(C16=""bob"",D16=""Bob"",E16=""Bob"",B16=""x""),1,2)"

?
?

I have no clue?

thanks




Jeff

 

[Jeff P]

Actually, I can utilize the code you provided in a different manner. Requires a few more steps but it'll work.

Thanks

Jeff

 

[mirabeau]

How about this?

Sub Sort_Names_Bob()
Dim lr&, e
With Range("C:E")
lr = .Find("*", searchorder:=xlByRows, _
        searchdirection:=xlPrevious).Row
    For Each e In .Resize(lr)
    If e.Value = "Bob" Then Cells(e.Row, "h") = "x"
    Next
End With
End Sub

 

[Jeff P]

Fantastic!

Exactly what I was looking for.

Thanks, mirabeau!

Jeff