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"

Comparing two columns with string
Display two columns in data validation list but return only one - Guide
Vba select case string contains - Guide
Tweetdeck larger columns - Guide
Excel count occurrences of string in column - Guide
How to remove columns in word - Guide

2 responses

Answer 1 / 2

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 :)

juan
Jul 31, 2015 at 08:16 AM

I'm not sure how this will work I copy and paste, but I don't see the result that I expected
Thx

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.

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

Answer 2 / 2

juan
Jul 30, 2015 at 01:55 PM

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)

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

Similar discussions

Comparing columns and returning value

Excel Macro/Code help: Creating sheet for each name in column of another sheet

formula if you have credit in one column, debit in next column

Similar discussions

Subscribe To Our Newsletter!