SOLVED

Excel Help with filtering data

%3CLINGO-SUB%20id%3D%22lingo-sub-1919072%22%20slang%3D%22en-US%22%3EExcel%20Help%20with%20filtering%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1919072%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20assistance%20with%20a%20spreadsheet%20where%20I%20want%20to%20compare%202%20columns.%20Column%20A%20contains%20cells%20where%20each%20cell%20contains%20only%201%20physician's%20name.%20Column%20B%20contains%20cells%20where%20there%20are%20multiple%20physicians'%20names.%20Is%20there%20a%20way%20to%20designate%20the%20rows%20where%20Column%20A%26nbsp%3B(physician)%20is%20not%20in%20Column%20B%20(multiple%20physicians)%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20so%20much!%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1919072%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1919238%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Help%20with%20filtering%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1919238%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F876600%22%20target%3D%22_blank%22%3E%40Shelfrich%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESelect%20the%20range%20in%20column%20A%20with%20names.%20I'll%20assume%20that%20A2%20is%20the%20active%20cell%20in%20the%20selection.%3C%2FP%3E%0A%3CP%3EOn%20the%20Home%20tab%20of%20the%20ribbon%2C%20select%20Conditional%20Formatting%20%26gt%3B%20New%20Rule...%3C%2FP%3E%0A%3CP%3ESelect%20'Use%20a%20formula%20to%20determine%20which%20cells%20to%20format'.%3C%2FP%3E%0A%3CP%3EEnter%20the%20formula%20%3DISERROR(MATCH(%22*%22%26amp%3B%24A2%26amp%3B%22*%22%2C%24B%3A%24B%2C0))%3C%2FP%3E%0A%3CP%3EClick%20Format...%3C%2FP%3E%0A%3CP%3EActivate%20the%20Fill%20tab.%3C%2FP%3E%0A%3CP%3ESelect%20a%20color%20to%20highlight%20the%20cells.%3C%2FP%3E%0A%3CP%3EClick%20OK%2C%20then%20click%20OK%20again.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1919401%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Help%20with%20filtering%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1919401%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B-%20%26nbsp%3BI%20am%20receiving%20a%20error%20message%20that%20says%20%22There's%20is%20a%20problem%20with%20this%20formula.%20Not%20tring%20to%20type%20a%20formula%3F%20When%20the%20first%20character%20is%20an%20equal%3D%20or%20minus%20-sign%2C%20Excel%20thinks%20it%20is%20a%20formula.%22%20This%20is%20what%20I%20typed%20in%3A%3C%2FP%3E%3CP%3E%3DISERROR(MATCH(%22%22*%22%22%26amp%3B%24A2%26amp%3B%22%22*%22%22%2C%24B%3A%24B%2C0))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I need assistance with a spreadsheet where I want to compare 2 columns. Column A contains cells where each cell contains only 1 physician's name. Column B contains cells where there are multiple physicians' names. Is there a way to designate the rows where Column A (physician) is not in Column B (multiple physicians)?

 

Thank you so much!

  

 

 

 

 

3 Replies
Highlighted

@Shelfrich 

Select the range in column A with names. I'll assume that A2 is the active cell in the selection.

On the Home tab of the ribbon, select Conditional Formatting > New Rule...

Select 'Use a formula to determine which cells to format'.

Enter the formula =ISERROR(MATCH("*"&$A2&"*",$B:$B,0))

Click Format...

Activate the Fill tab.

Select a color to highlight the cells.

Click OK, then click OK again.

Highlighted

@Hans Vogelaar -  I am receiving a error message that says "There's is a problem with this formula. Not tring to type a formula? When the first character is an equal= or minus -sign, Excel thinks it is a formula." This is what I typed in:

=ISERROR(MATCH(""*""&$A2&""*"",$B:$B,0))

 

Highlighted
Best Response confirmed by Shelfrich (New Contributor)
Solution

@Shelfrich 

You have used "" where I had ".

 

If you use comma as decimal separator, the formula should be

 

=ISERROR(MATCH("*"&$A2&"*";$B:$B;0))