Comparing two columns with string

[Closed]
Report
Posts
1
Registration date
Thursday July 16, 2015
Status
Member
Last seen
July 16, 2015
-
Posts
14
Registration date
Thursday July 16, 2015
Status
Member
Last seen
August 14, 2015
-
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 replies

Posts
14
Registration date
Thursday July 16, 2015
Status
Member
Last seen
August 14, 2015
1
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 :)
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

I'm not sure how this will work I copy and paste, but I don't see the result that I expected
Thx
Posts
14
Registration date
Thursday July 16, 2015
Status
Member
Last seen
August 14, 2015
1
i think u forgot to hit Ctrl+shift+enter.
Posts
14
Registration date
Thursday July 16, 2015
Status
Member
Last seen
August 14, 2015
1
http://www.fast-files.com/getfile.aspx?file=96381


i uploaded the file,download it and reply me if this work for u
thanks
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
Posts
14
Registration date
Thursday July 16, 2015
Status
Member
Last seen
August 14, 2015
1
hello juan
put =IF(A1>B1,1,"") in c1 and =IF(A1>B1,"",0)
>
Posts
14
Registration date
Thursday July 16, 2015
Status
Member
Last seen
August 14, 2015

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