Showing only rows where the wording is similar

%3CLINGO-SUB%20id%3D%22lingo-sub-1584083%22%20slang%3D%22en-US%22%3EShowing%20only%20rows%20where%20the%20wording%20is%20similar%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1584083%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20there%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20spreadsheet%20is%20attached.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20figure%20out%20how%20to%20only%20show%20rows%20where%20the%20wording%20in%20column%20B%20is%20used%20in%20column%20A.%20Would%20there%20be%20a%20function%20for%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAn%20example%20would%20be%20row%2044.%20B44%20is%20'1%20Archway%20Mews'%20and%20A44%20is%20'1%20Archway%20Mews%20Freehold%20Limited'.%20This%20would%20be%20a%20row%20I%20need.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20greatly%20appreciated%20as%20I'm%20stumped!%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EThank%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1584083%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1584106%22%20slang%3D%22en-US%22%3ERe%3A%20Showing%20only%20rows%20where%20the%20wording%20is%20similar%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1584106%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F757015%22%20target%3D%22_blank%22%3E%40jshawyer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Etry%20this%20formula%3C%2FP%3E%3CP%3E%3DIFERROR(SEARCH(B2%2CA2)%2C0)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eyou%20can%20also%20check%20the%20fuzzy%20search%20add-in%20for%20Excel%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.microsoft.com%2Fen-us%2Fdownload%2Fconfirmation.aspx%3Fid%3D15011%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.microsoft.com%2Fen-us%2Fdownload%2Fconfirmation.aspx%3Fid%3D15011%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1584130%22%20slang%3D%22en-US%22%3ERe%3A%20Showing%20only%20rows%20where%20the%20wording%20is%20similar%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1584130%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F757015%22%20target%3D%22_blank%22%3E%40jshawyer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20your%20first%20sheet%20I%20added%20a%20column%20and%20in%20it%20used%20FIND%20to%20identify%20companies%20where%20your%20stated%20condition%20exists.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20sheet%202%2C%20I%20added%20a%20FILTER%20function%20(which%20requires%20the%20newest%20edition%20of%20Excel)%20that%20filters%20and%20shows%20only%20the%20companies%20identified%20in%20Column%20A%20of%20the%20first%20sheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20don't%20have%20access%20to%20the%20FILTER%20function%2C%20you%20can%20still%20use%20the%20menu%20item%20Data.....Sort%20to%20accomplish%20the%20same%20on%20your%20sheet%201%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1584283%22%20slang%3D%22en-US%22%3ERe%3A%20Showing%20only%20rows%20where%20the%20wording%20is%20similar%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1584283%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20both%20very%20much!%20Exactly%20what%20I%20needed.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1584298%22%20slang%3D%22en-US%22%3ERe%3A%20Showing%20only%20rows%20where%20the%20wording%20is%20similar%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1584298%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F757015%22%20target%3D%22_blank%22%3E%40jshawyer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThose%20two%20responses%20illustrate%20the%20truth%20that%20there%20are%2C%20within%20Excel%2C%20many%20ways%20to%20resolve%20situations.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi there,

 

My spreadsheet is attached.

 

I am trying to figure out how to only show rows where the wording in column B is used in column A. Would there be a function for this?

 

An example would be row 44. B44 is '1 Archway Mews' and A44 is '1 Archway Mews Freehold Limited'. This would be a row I need.

 

Any help would be greatly appreciated as I'm stumped!


Thank you.

 

4 Replies

@jshawyer 

 

try this formula

=IFERROR(SEARCH(B2,A2),0)

 

you can also check the fuzzy search add-in for Excel

https://www.microsoft.com/en-us/download/confirmation.aspx?id=15011

 

 

@jshawyer 

 

On your first sheet I added a column and in it used FIND to identify companies where your stated condition exists.

 

On sheet 2, I added a FILTER function (which requires the newest edition of Excel) that filters and shows only the companies identified in Column A of the first sheet.

 

If you don't have access to the FILTER function, you can still use the menu item Data.....Sort to accomplish the same on your sheet 1

Thank you both very much! Exactly what I needed. 

@jshawyer 

Those two responses illustrate the truth that there are, within Excel, many ways to resolve situations.