compare two cells

Originally Posted by mrice

In this case, what do you define as a word?

Is it any characters (including numbers) between two spaces or at the start/end of the string?

Or do you want to exclude substrings which contain numbers?

I want to compare anything. Numbers/Letters or combo. These would be defined withing a space on both sides of the word.

Example: 6858 3/4 in hcs hex die

"6858" , "3/4" , "in" , "hcs" , "hex" , "die" are all words

Originally Posted by zbor

You can:

Insert one more column in A (for result)
Switch (former) A and B column (now B and C) so B became C and viceversa.
Select C column and Data -> Text to columns -> Delimited and use space for delimiter
in A column write: =SUMPRODUCT(--(C1:L1<>""), COUNTIF(B:B;"*"&C1:L1&"*")) and pull down.

If result is greater then 0 you have match.
I left number so you can hoe many matches you get.

I can't get this to give good readings either.

691....... MOWER BLADE 20"MURRAY......... BLACK PVC BOOT 7-SZ


This one yielded a 691 in your formula. Dosn't seem right

Originally Posted by NBVC

This formula is C2 might work for what you want...

=IF(SUMPRODUCT(--ISNUMBER(MATCH("*"&TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",100)),1+(100*(ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1))-1)),100))&"*",$B$2:$B$5,0))),"similar","no match")

In your case the word "die" exists in all the column A entries, and therefore they will all have matches in column B

Sorry, should of been more clearly, I am only comparing the two cells within the row.

a1 to b1
a2 to b2 etc.

Originally Posted by NBVC

This formula is C2 might work for what you want...

=IF(SUMPRODUCT(--ISNUMBER(MATCH("*"&TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",100)),1+(100*(ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1))-1)),100))&"*",$B$2:$B$5,0))),"similar","no match")

In your case the word "die" exists in all the column A entries, and therefore they will all have matches in column B

I tinkered with this and couldn't get it to work

Example

WAND BRASS 12 CURVED".................. WAND BRASS 12" CURVED

This yields a "no match"

Originally Posted by NBVC

I don't. See attached.

Note that the first 3 matched, but Curved" didn't due to the " at the end of the word.

Not sure what the diff was but this is working now once I took the formula from your spreadsheet and pasted it into note pad and re-copied/pasted back into excel.

I think this will lighten the workload a bit.

Thanks