check whether all the words in a cell appear in another cell even if ordered differently

Hello,

I'd like a formula that returns true if ALL words within a cell (See A1 below) appear on another cell (B1), regardless of word order. Thanks in advance.

Cell A1: House Tree Cat

Cell B1: Bench Cat Pier Tree Door House

Update: see attachment below
Data_Workbook.xlsx

Try these in A2:A3

	A	B
1	House Tree Cat	Bench Cat Pier Tree Door House
2	3	In A2: =LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1
3	TRUE	In A3: =COUNT(SEARCH(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),(ROW(INDIRECT("1:"&A2))-1)*LEN(A1)+1,LEN(A1))),B1))=A2

And BTW: are you still using Excel 2003? If not please update your profile.

If you are not familiar with how to update your profile find the dark blue menu at the top of this page that says

Forum Actions

. Click that and 'Edit Profile'.

Originally Posted by daatr

Hi Dave,

Thanks for your response. Using your information, i'm getting a false response on A3. Please see attached. Attachment 826338

OK. I'll attach a workbook with the formulas.

And in Excel 2007 you may need to array enter that formula.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Thank you very much for your help. The array enter fixed it, my apologies for not being aware of it.

1. This is nearly what I wanted, the only thing that's left is that the formula is still getting false matches with partial words, so for example, if I put: "Do House Tree Cat" as the value for A1, I still get a true match because Do is a partial match of Door. Is there a way to correct it so it only returns true if all words are an exact match?

2. I realize I'd appreciate your assistance on one extra variation on the first formula which can go in Cell A4. This formula is the same as 1 (recognizes exact word matches), with the addition of recognizing plural words.

so for example, if B1: is Cats Pier Tree 54 68s

a. if A1 is: Trees Cat -> formula would return true (plural and non-plural words match for both directions).
b. If A1 is: 54s Tree OR 68 Tree – the formula will return false as numbers can’t be plural

Edited: Too much commentary. Needed to be reduced.

WOW! That is a lot of conditions/exceptions. You mentioned none of this in the original post.

I am not confident I can do it without several extra steps/lookup-tables/et al ... especially in earlier Excel products. Other contributors will hopefully prove me wrong. We'll see.

Meanwhile I will see if I can summon community help on this one. Please stand by.

Good deal.

In the meantime please upload an Excel workbook with representative data and desired results ... hand typed. Sections Before and After.

By the way. Is the solution also to be Case Sensitive?

with an UDF,
if you have 2 "Cats" in the first string and only 1 "Cat" in the 2nd, is that false or true
for exemple "Cat cats dog mouse" and "cat dogs mouses" ???

Please Login or Register  to view this content.

EDIT : as FlameRetired asked, this solution is "for the moment" not case sensitive.

Sorry, I missed your message. Here's data that provides clear examples that would be rejected/approved by the code with reasons. This is not case sensitive.

Workbook_Data.xlsx

i saved it as a ".xls", so that should work on your 2007, i hope.