Partial match of a text

[aikhalif]

Hi,
I am trying to match a portion of the text in a given array and retur
the corresponding complete value of the array.

Example:

A B C
command command command
exc axisid excel
metrics myer prop sport metrics
myer plc sports metrics myer prop
axis pty excel axisid
pivot toblerone pivott table
toble pivott table toblerone
chair 6 chair 6 chair

Coloumn is the value that I want to search in Column B. IF full/part o
col-A in present in Col-B, then display the complete text of column B i
Column C. Coloumn is the output I want to get.

However, I am able to get all value with the formula
=VLOOKUP("*"&A4&"*",B$4:B$11,1,FALSE).

except, for 4th and 5th line as Column A contains more words that ar
nto in Column B.

I would appreciate if you could assist me on this.

Cheers,
A

 

[Claus Busch]

Hi Ai,

Am Tue, 14 May 2013 13:45:00 +0100 schrieb aikhalif:

A B C
command command command
exc axisid excel
metrics myer prop sport metrics
myer plc sports metrics myer prop
axis pty excel axisid
pivot toblerone pivott table
toble pivott table toblerone
chair 6 chair 6 chair

try:
=INDEX($B$1:$B$10,MATCH(IF(ISNUMBER(FIND(" ",A2)),LEFT(A2,FIND(" ",A2)-1)&"*","*"&A2&"*"),$B$1:$B$10,0))


Regards
Claus Busch

 

[Ron Rosenfeld]

IF full/part of
col-A in present in Col-B, then display the complete text of column B in
Column C.

If I understand you correctly, if you have more than one word in column A, you want to return in column C a match of either the first word, or the second word.

What about if the first word matches one entry in column B, and the second word matches a different entry?
What if the two words match something in column B, and the first and/or second words match something different?

For a start, the following User Defined Functon checks column B with each word in the Column A item separately, and returns the First match it finds.
As you "play" with it, you may discover that some of the logic needs to be modified; so post back with more detailed requirements (if needed).

To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=FindFullPartialSubstring(A1,$B$1:$B$7)

in some cell.

====================================
Option Explicit
Function FindFullPartialSubstring(StringToFind As String, _
RangeToSearch As Range) As String
Dim c As Range
Dim vWords As Variant
Dim i As Long
vWords = Split(StringToFind, " ", -1, vbTextCompare)
For i = 0 To UBound(vWords)
With RangeToSearch
Set c = .Find(what:=vWords(i), after:=.Item(.Count), _
LookIn:=xlValues, lookat:=xlPart, _
searchorder:=xlByRows, MatchCase:=False, _
searchdirection:=xlNext)
End With
If Not c Is Nothing Then
FindFullPartialSubstring = c.Text
Exit For
End If
Next i
End Function
=================================

 

[aikhalif]

Thank you for responses Ron Rosenfeld and ... I appreciate it.

I could get the result. However, my analysis requires one more highe
level than previouusly mentioned - Sample provided below.

ColumnA ColumnB ColumnC ColumnD
command City command command
exc City axisid excel
Command Town myer prop command 1
myer plc Suburb sports metrics myer prop
axis pty State excel axisid
pivot Country toblerone pivott table
toble State pivott table toblerone
chair p/l City 6 chair 6 chair
chair loop CIty command 1 6 chair
ex myer prop State myer prop
super sports chair Country sports metrics
500 electrics State -
SM co City Sports metrics
sports chair 6 country 6 chair


I would like this to be as an excel formula, than as a VB coding.

Thanks guys

 

[Ron Rosenfeld]

Thank you for responses Ron Rosenfeld and ... I appreciate it.

I could get the result. However, my analysis requires one more higher
level than previouusly mentioned - Sample provided below.

ColumnA ColumnB ColumnC ColumnD
command City command command
exc City axisid excel
Command Town myer prop command 1
myer plc Suburb sports metrics myer prop
axis pty State excel axisid
pivot Country toblerone pivott table
toble State pivott table toblerone
chair p/l City 6 chair 6 chair
chair loop CIty command 1 6 chair
ex myer prop State myer prop
super sports chair Country sports metrics
500 electrics State -
SM co City Sports metrics
sports chair 6 country 6 chair


I would like this to be as an excel formula, than as a VB coding.

Thanks guys.

With regard to an Excel formula, rather than a User Defined Function, I will leave that for someone else. It could probably be done with multiple helper columns, or long and difficult to maintain formulas.

So far as your additional requirements, the examples you post do not allow me to make unambiguous rules.

For example:

ex myer prop --> myer prop BUT ex also provides a partial match with excel
SM co --> Sports Metrics BUT SM co does NOT match Sports Metrics, and co matches command

It is also not clear to me what rule should be applied for:
command --> command
Command --> command 1

On your data above, the UDF I provided returns the following:
command command
exc excel
Command command
myer plc myer prop
axis pty axisid
pivot pivott table
toble toblerone
chair p/l 6 chair
chair loop 6 chair
ex myer prop excel
super sports chair sports metrics
500 electrics
SM co command
sports chair 6 sports metrics

 

[aikhalif]

Ron,
SM co indicates the short version of Sports Metrics, considering th
first letters S and M.

As for ex, i just want an example where there are dummy variables befor
the word and variables after that are consiedered and matched.

Thanks

 

[Ron Rosenfeld]

Ron,
SM co indicates the short version of Sports Metrics, considering the
first letters S and M.

As for ex, i just want an example where there are dummy variables before
the word and variables after that are consiedered and matched.

Thanks.

Oh, it is easy to see what you want to do.

The problem with doing it using a formula (or a UDF) is that there does not seem to be a logical rule that will apply to all instances. The issue with SM co is only one of several problems in what you present.
In the absence of rules (and examples, such as you have presented, are NOT logical rules), it is not possible to devise a formula that will cover all the variability that you have presented.

I would suggest since you cannot create logical rules, for you to create a lookup table (or manually clean up your original data).