• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data list

K

kasthuri

New Member
Hi ,


I need help on finding the matching values.

Column A - Tan Ai Ling

Column B - Ai Ling Tan


Column C should return the matching names - Ai Ling Tan.


What formula should I use for this. HOpe you can help me. Thanks.
 
Hi Kasthuri ,


Can you either explicitly mention a few more details or give more examples ?


The example you have given for column A - are the three words in one cell or in three different cells ? The same issue is with the three words in column B.


If you find it time-consuming to explain , just upload a sample workbook. For how to do this , check out this link :


http://chandoo.org/forums/topic/posting-a-sample-workbook


Narayan
 
Hi Narayan,


Its in one cell both Column A and Column B.


I want column C to return the matching names from Column B and check in the whole column of A .


Thanks for your quick respond.


Regards,

Kasthuri.
 
Hi Kasthuri ,


Your requirement is somewhat similar to what was posted in these forums earlier ; check out the replies to this post :


http://chandoo.org/forums/topic/how-to-sort-words-in-a-cell-in-alphabetical-order


Since some helper columns will have to be used , it'll be nice if you could upload a sample workbook. Go through the above link and see if it helps.


Narayan
 
Hi Kasthuri ,


Along with each name in the cells , there is also additional text , separated from the name by a /. What is to be done with this additional text ?


Narayan
 
I just want to make a comparison if Column B value contain in Column A. For example if cell B1 = Ahmad Azman Bin Ahmad Saud which Ahman Azman Ahmad Saud does contain in cell A1 so column C1 should be True
 
kasthuri,

Still not sure why some things would be true, others false. Given this set:

[pre]
Code: 
Col A	            Col B                            Col C
bob tom smith	    bob tom smith sue
a b c d	            b c
red yellow green    red yellow green blue
[/pre]
Would all 3 lines be marked True? Or would only C2 be true since ALL of the contents in B2 are found in column A? Or, are we actually looking at col A and seeing if all the items are in col B, in which case C1 and C3 would be True?
 
Hi Luke ,


I'll answer for Kasthuri , by posting a sample from the workbook :

[pre]
Code: 
Ahmad Azman Ahmad Saud/MY/Asia/Celestica	 Ahmad Azman Bin Ahmad Saud
Anis Atiqah Azman/MY/Asia/Celestica              Anis Atiqah Binti Azman
Asmawi Abas/MY/Asia/Celestica                    Asmawi Bin Abas
Arzuani Abd Rahim/MY/Asia/Celestica	         Arzuani Binti Abd Rahim
Azman Abd Samad/MY/Asia/Celestica	         Azman Bin Abd Samad
Azamuddin Abdul Raof/MY/Asia/Celestica           Azamuddin Bin Abdul Raof
Azman Ab Hamid/MY/Asia/Celestica	         Azman Bin Ab Hamid
Azly Abu Bakar/MY/Asia/Celestica	         Azly Bin Abu Bakar
[/pre]

These are the two columns of data ; the names have to be separated into their words , and then each of the words in the second column has to be compared with the words in the first column to see whether there is any match.


Narayan
 
Hi Kasthuri ,


I hope Luke or someone else can help you out , since I will be able to do something only tomorrow morning.


This calls for quite a few helper columns ; the words in the first column till the slash , need to be separated into columns ; the same should be done with the words in the second column.


If the words from the first column are more in number than the words from the second column , then we see if the words from the second column occur amongst the words from the first column ; else we check if the words from the first column occur amongst the words from the second column.


Narayan
 
Here's my best shot so far. Using this UDF, your formula would be:

=PartialExists(B2,A:A)


To install this UDF, right click on sheet tab, view code, Insert module, paste this in:

[pre]
Code: 
Function PartialExists(StartCell As String, SearchRange As Range) As Boolean
Dim WordCount As Integer
PartialExists = False
WordCount = Len(StartCell) - Len(WorksheetFunction.Substitute(StartCell, " ", "")) + 1

Dim MyWords(1 To 100) As String
Dim LastSpace As Integer
Dim NextSpace As Integer

'Build list of words

LastSpace = 1
For i = 1 To WordCount
If i = WordCount Then
NextSpace = 999
Else
NextSpace = WorksheetFunction.Find(" ", StartCell, LastSpace)
End If
MyWords(i) = Mid(StartCell, LastSpace, NextSpace - LastSpace)
LastSpace = NextSpace + 1
Next i

'check if any word exists
Dim xExists As Variant
For i = 1 To WordCount
On Error Resume Next
xExists = SearchRange.Find(MyWords(i))
On Error GoTo 0
If xExists <> "" Then
PartialExists = True
Exit Function
End If
Next i
End Function
[/pre]
NOTE: This is literally checking if the any word in column B exists anywhere within col A. So, if the word was "sled" and col A has "bobsled", formula will evaluate to True. Since it looks like you are dealing with names, I'm not sure if this is an issue or not.
 
You must log in or register to reply here.
Back
Top