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]

We will get you through it, if need be. I will start on the vba code version shortly.

 

[johnnyL]

Here is a version of VBA code for you:

Sub faeryluv()
'
    DisplayedTextWhenMatchFound = "Yes"                                     ' <--- Set this to desired text to display when a match is found
'
    LastrowPhysicians = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row
    LastrowReferrals = Sheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row
'
    Sheets("Sheet1").Range("A2:A" & LastrowReferrals).ClearContents         ' Clear the previous results from the Column A
'
    For PhysiciansLoop = 2 To LastrowPhysicians
        For ReferralsLoop = 2 To LastrowReferrals
            If Application.WorksheetFunction.CountIf(Sheets("Sheet1").Range("B" & ReferralsLoop), "*" & Sheets("Sheet2").Range("A" & PhysiciansLoop) & "*") > 0 Then
                Sheets("Sheet1").Range("A" & ReferralsLoop) = DisplayedTextWhenMatchFound
            End If
        Next
    Next
End Sub

Download for workbook that mimics original post data with a click button added:
Workbook with click button to update list

This assumes that that the sheet2 has been changed to a format of FirstName LastName

I'm sure the better coders here will start posting responses here that make me look like an idiot, but hey, the code I posted works, no matter how overthought it may be. We all learn from other responses!

 

[faeryluv]

@johnnyL
You're amazing!!!
View attachment 42080

I don't know how you did this, but it works great! You have saved me so much time!

Here's a virtual thank-you gift:

 

[faeryluv]

@johnnyL is this supposed to pick up the entire column? There's one towards the bottom that it's not picking up no matter how many times I click the button. Can I privately share my sheet with you?

 

[johnnyL]

@johnnyL is this supposed to pick up the entire column? There's one towards the bottom that it's not picking up no matter how many times I click the button. Can I privately share my sheet with you?

Yes it picks up the entire columns.

two things you can try:
1) save the excel workbook and then reopen it and try the code
2) double check that your physician list name exactly matches the name that is part of the referral list. Capital or lowercase doesn't matter, but the spelling and space in between have to match

If you still don't solve the problem, just post here the name from the physician list and the cell from the referral list that should be matching.

 

[faeryluv]

It's working PERFECTLY now!!

For some reason there was an extra space in front of some of the names. I used the TRIM formula to remove any extra spaces.

Thanks again!! MUCH appreciated, my friend!!

 

[johnnyL]

Excellent! Glad it is working for you @faeryluv