Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count if with partial text match (edited X2) | Excel Worksheet Functions | |||
Count if with partial text match | Excel Worksheet Functions | |||
Find partial match from column A,B and fill partial match in C? | Excel Discussion (Misc queries) | |||
Vlookup using a partial text match | Excel Worksheet Functions | |||
partial/absolute text match | Excel Worksheet Functions |