Finding and Highlight a row based on a string of text within a cell

[andyk]

Hi

I have 2 documents,

document A holds a list of colleagues within a department.
document B holds a finance report

within document B there is a cell that contains the surname of the colleague in-between some codes

For Example Cell A5 will house 45675-655B-Smith-76434

Im looking for a solution that would automate ( or as close as possible ) the process of searching through the text string in Cell A5 against the list of colleagues within Document A and highlighting the matches

Is this possible and if so any help would be appreciated

 

[Richard Schollar]

Hi

Do you have just the surnames of the colleagues in a distinct column in Document A?

If so, you can use a MATCH formula to indicate matches:

=ISNUMBER(MATCH("*" & A5 & "*",'[Document A]Sheet1'!A:A,0)) somewhere in Doc B and copy down for the number of rows you have. All the TRUEs returned will indicate a matc (assumes the surnames alone are in col A of Sheet1 in Document A).