Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 3
Default Partial match of a text

Hi,
I am trying to match a portion of the text in a given array and return 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

Column A is the value that I want to search in Column B. IF full/part of column A is present in Column B, then display the complete text of column B in Column C. Coloumn C 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 are not in Column B.

I would appreciate if you could assist me on this.

Cheers,
Ai

Last edited by aikhalif : May 14th 13 at 01:55 PM
  #2   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 3,872
Default Partial match of a text

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
  #3   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 1,045
Default Partial match of a text

On Tue, 14 May 2013 13:45:00 +0100, aikhalif wrote:

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
=================================
  #4   Report Post  
Junior Member
 
Posts: 3
Default

Thank you for responses Claus Busch and Ron Rosenfeld - 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 have Column A as company names, with coreesponding locations of those companies in Column B. Column C is the text I want to match against Column A. When there are similarirties in Column A, the search should then extend to Column B, and corresponding match among the Column c, should be mentioned in Column D, which is the output.

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

Thanks guys.

Last edited by aikhalif : May 15th 13 at 07:19 AM
  #5   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 1,045
Default Partial match of a text

On Wed, 15 May 2013 06:34:03 +0100, aikhalif wrote:


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


  #6   Report Post  
Junior Member
 
Posts: 3
Default

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.
  #7   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 1,045
Default Partial match of a text

On Wed, 15 May 2013 13:37:37 +0100, aikhalif wrote:


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).
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Count if with partial text match (edited X2) [email protected] Excel Worksheet Functions 1 June 2nd 09 04:47 PM
Count if with partial text match [email protected] Excel Worksheet Functions 0 June 2nd 09 03:49 PM
Find partial match from column A,B and fill partial match in C? Tacrier Excel Discussion (Misc queries) 4 October 24th 08 11:24 PM
Vlookup using a partial text match JGROVE Excel Worksheet Functions 1 August 15th 08 03:18 PM
partial/absolute text match george Excel Worksheet Functions 3 May 1st 06 06:15 AM


All times are GMT +1. The time now is 03:17 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"