Excel 2007 Like or Contains Expression

[Debra Lucius]

Hello,

I'm working with Excel 2007 and I need to be able to compare two columns where the second column contains value like column one.

Example
Col 1 CJA140
Col 2 CJA140-AUO; CJA140-V3; CHA140-A

I'm trying to create an expression that states if Col 1 is like or contains values in Col 2, then display results from Col 2.

Is this possible?

Thank you

 

[Claus Busch]

Hi Debra,

Am Fri, 16 Jan 2015 08:35:57 -0800 (PST) schrieb Debra Lucius:

Example
Col 1 CJA140
Col 2 CJA140-AUO; CJA140-V3; CHA140-A

your data in column B, the string to search in A1:
=IFERROR(INDEX(B$1:B$1000,SMALL(IF(ISNUMBER(SEARCH(A$1,B$1:B$1000)),ROW($1:$1000)),ROW(A1))),"")
Insert the array formula with CTRL+Shift+Enter and copy down


Regards
Claus B.

 

[dguillett]

Hello,

I'm working with Excel 2007 and I need to be able to compare two columns where the second column contains value like column one.

Example
Col 1 CJA140
Col 2 CJA140-AUO; CJA140-V3; CHA140-A

I'm trying to create an expression that states if Col 1 is like or contains values in Col 2, then display results from Col 2.

Is this possible?

Thank you

A macro solution. Place in sheet or regular module
Option Explicit
Sub comparecolumns()
Dim lra As Long
Dim mf As Range
Dim c As Range
lra = Cells(Rows.Count, "a").End(xlUp).Row
For Each c In Range("a1:a" & lra)
Set mf = Columns(2).Find(What:=c, After:=Cells(1, 2), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not mf Is Nothing Then Cells(Rows.Count, 4).End(xlUp)(2) = mf.Value
Next c
End Sub