Partial Match Search and highlight cells not equal to

[faeryluv]

My Excel brain is just not working this week. I have two problems and looking for two formulas. If anyone can help, I'd appreciate it more than you know!

I have a huge report from our Helpline that lists the caller's name and info along with what doctor referred them. I have to filter the report for my boss to show St. John Physicians only. The only way I know how to do this is to go line by line through hundreds of rows to search for 50 different doctor names, which is just so time-consuming.

I've created a list of all of the doctors on Sheet 2, and I've inserted a column on sheet one to look up each doctor to see if it is a St. John doctor on my Sheet2. I just can't get a formula to work for me. I've spent so much time on it and I have so much other work to do. The problem I am running into, I think, is that Helpline enters the doctors names in so many different ways.

Any help is much appreciated!

(all names in my example sheets are fictitious for confidentiality)

Problem #1

SHEET1

SHEET2

For my second question, all I need is to highlight any cell in column B that does not equal "Active". I have tried conditional formatting and formulas, but can only get it to work if I make it highlight a cell containing the word "pending". That means I will have to set multiple conditions since there's many options other than "Active"; I have only listed two others here (Pending, and Inactive). Again, my brain is just not working lately. I usually am able to Google these problems, but I just can't seem to figure it out. Thanks again, in advance, for any help!

Problem #2

 

[johnnyL]

For Problem #2

highlight the B2 range of data
conditional formatting
New Rule
Format only cells that contain
Cell Value/not equal to/Active
Format.../Fill tab/select color

 

[faeryluv]

Thank you, johnnyL! I can't believe I missed that! Doh! You're saving my day!

 

[faeryluv]

I hope that I made sense when explaining problem #1.

 

[johnnyL]

I think your problem 1 would be much easier if you changed your Sheet 2 from LastName, FirstName format to FirstName LastName format.

 

[faeryluv]

I think your problem 1 would be much easier if you changed your Sheet 2 from LastName, FirstName format to FirstName LastName format.

Oh definitely! That's easy to do; I have that formula...

 

[johnnyL]

Ok this is ticking me off! I am not familiar with starting with a larger string in a cell and looking for matches in a smaller string. Hopefully someone else can chime in, I think I am about to toss this computer into my pool at this point. UGGGGG!

Normally you would check a smaller string (include wildcards) against a larger string. I am not familiar with the vice versa, sorry @faeryluv.

 

[faeryluv]

I am so sorry to frustrate you. I tend to complicate things too much. Maybe there's an easier way. I am off work till Tuesday. I'll go back to the drawing board and work through the process then. Maybe after a nice, long weekend my brain will work again. ?

 

[johnnyL]

@faeryluv Since formula version is making my hair hurt, is vba code an acceptable path?

 

[faeryluv]

@faeryluv Since formula version is making my hair hurt, is vba code an acceptable path?

Lol! Made your hair hurt, huh? Well, I'm not that familiar with vba code. I've used macros before, but just not sure I'd understand it.