Comparing two columns with string

Closed
apbbsr Posts 1 Registration date Thursday July 16, 2015 Status Member Last seen July 16, 2015 - Jul 16, 2015 at 02:20 PM
M.Hanzla Posts 14 Registration date Thursday July 16, 2015 Status Member Last seen August 14, 2015 - Aug 2, 2015 at 03:38 AM
I have two columns :A (shorter list) and B (longer list). Is there a way to find all instances of B which may have any string from A? For e.g.

COLUMN A
ABCDE 123456
Leaf Super

COLUMN B
SD ABCDE 123456 Specialist,
Fork Number Leaf Super
ABCDE 123456

SHOULD FIND MATCH IN ROW 1, AND 3 IN COLUMN B FOR "ABCDE 123456" AND IN ROW 2 FOR "LEAF SUPER"

2 responses

M.Hanzla Posts 14 Registration date Thursday July 16, 2015 Status Member Last seen August 14, 2015 1
Jul 31, 2015 at 05:54 AM
hello apbbsr,
i m writing a formula for your data given which i putted in c1 n copy down u can change the range according to your need
=IFERROR(INDEX($A$1:$A$2,MATCH(1,COUNTIF(B1,"*" & $A$1:$A$2 & "*"),0)),"")
put it into c1 and dnt forget ctrl+shift+enter instead of single enter(array formula)
dnt forget to thnx if worked :)
I'm not sure how this will work I copy and paste, but I don't see the result that I expected
Thx
0
M.Hanzla Posts 14 Registration date Thursday July 16, 2015 Status Member Last seen August 14, 2015 1
Aug 2, 2015 at 03:36 AM
i think u forgot to hit Ctrl+shift+enter.
0
M.Hanzla Posts 14 Registration date Thursday July 16, 2015 Status Member Last seen August 14, 2015 1
Aug 2, 2015 at 03:38 AM
http://www.fast-files.com/getfile.aspx?file=96381


i uploaded the file,download it and reply me if this work for u
thanks
0
I want to compare the values is two column, say A1>b1 if true put a one in column c1, if false put a zero in d1
thx
M.Hanzla Posts 14 Registration date Thursday July 16, 2015 Status Member Last seen August 14, 2015 1
Jul 31, 2015 at 06:03 AM
hello juan
put =IF(A1>B1,1,"") in c1 and =IF(A1>B1,"",0)
0
juan > M.Hanzla Posts 14 Registration date Thursday July 16, 2015 Status Member Last seen August 14, 2015
Jul 31, 2015 at 08:07 AM
this is what I have now, I'm looking for a way to have one formula that will place the one or zero on the right column
Thx
0