• 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.

VBA

J

Jayadev Sarangi

Member
Hello, I need some VBA help on the below requirement.


I have two sheets. Sheet 1 has a name called "Jay" lets say. Sheet2 has a text string with Jay, lets say like " jfjhljafvl11/2/12Jay-ext kjdhj". My question is how to get the name "Jay" out of the string and put into next cell in next column.


Kindly guide me. I need it urgently for some priority matter.


Regards

Jayadev Sarangi
 
Hi Jayadev,


When you already have "Jay" in a sheet, then why do you want to extract it from another string.


Also, are you following any set pattern in the second sheet i.e. in strings like "jfjhljafvl11/2/12Jay-ext kjdhj" is there any common property?
 
Hello Debraj, Thanks a lot for the quick reply but I need something as per below.


Sheet1 Cell A1 has "jfjhljafvl11/2/12Jay-ext kjdhj"

Sheet2 Cell A1 has "Jay"

I want "Jay" in Sheet1 Cell B1 next to "jfjhljafvl11/2/12Jay-ext kjdhj"


I hope I explained it clearly.


Regards

Jayadev
 
Hi Jayadev ,


Are you looking for something like this :


=IF(IFERROR(SEARCH(Sheet2!A1,Sheet1!A1),0)>0,Sheet2!A1,"Not found")


Using VBA :

[pre]
Code: 
If InStr(1, Worksheets("Sheet1").Range("A1").Value, Worksheets("Sheet2").Range("A1").Value) > 0 Then
Worksheets("Sheet1").Range("B1").Value = Worksheets("Sheet2").Range("A1").Value
End If
[/pre]
Narayan
 
Hi Jaydev..


In Excel > VBA > insert a New Module and paste the below function.

[pre]
Code: 
Function MatchInArea(MatchText As String, CheckRange As Range)
Dim cell As Range, Found As Boolean
For Each cell In CheckRange
If InStr(1, MatchText, cell.Value, vbTextCompare) > 1 Then
MatchInArea = cell.Value
Found = True
Exit For
End If
Next cell
If Found = False Then MatchInArea = "No Match Found"
End Function[/pre]

Now in Sheet1. B1 write formula as

=MatchInArea(A1,Sheet2!A1:A20)


where A1 contain "jfjhljafvl11/2/12Jay-ext kjdhj" and "Jay" is somewhere in between Sheet2 >> A1:A20


Problem :

If in Sheet2 >> A1:A20 somewhere a text contain "kjdhj" then it will not display "Jay", but it will display "kjdhj"


Please let me know if you are facing any issue.. :)
 
Hi Narayan,


Thanks for the quick help. The VBA works but I have to update the same for a range of rows may be 30000 or 40000 rows.


Kindly help.


Regards

Jayadev
 
Hello Debraj,


Thanks for the help. I followed your instructions and it works perfectly fine. So everytime I just need to copy paste the formula for the entore range, right?


I hope someday I will be able to write a VBA code like you.


Thanks a ton :)


Regards

Jayadev
 
Hi Jayadev..

Happy to help..


No, You dont have to paste the code everytime, You can use the Function normally like all other Function, (SUM, IF , Vlookup).. You can drag this function, or may be you need to write formula as


Code: 
=PERSONAL.XLSB!MatchInArea($A1,Sheet2!$A$1:$A$20)


Just google for PERSONAL.XLSB, and it will be your first step for your VBA Target..

Best of Luck..

Regards,

Deb
 
Thanks Debraj,


just a modification to the above code, instead of having a exact match can we search for the contains match. for example, if in sheet2, I have Jayadev Sarangi, instead of Jay, it should take as Jayadev Sarangi but search by Jay only.


Because the name may come in full or short, so it would be easier to find that way.


Regards

Jay
 
Hi Jayadev,


Please find the updated Function for the Search..

[pre]
Code: 
Function FSearch(SearchString As String, SearchRange As Range, Optional MinWord As Long = 3)
Dim MatchFound As Boolean, k As Integer
MatchFound = False
k = 1
For Each cell In SearchRange
For i = Len(SearchString) To 1 Step -1
text1 = Left(SearchString, i)
text2 = Right(SearchString, i)
text3 = Mid(SearchString, k, MinWord)
If Len(text1) < MinWord Or Len(text2) < MinWord Or Len(text3) < MinWord Then
Exit For
Else
Set found1 = SearchRange.Find(text1)
If Not found1 Is Nothing Then
FSearch = found1
MatchFound = True
Exit For
End If
Set found2 = SearchRange.Find(text2)
If Not found2 Is Nothing Then
FSearch = found2
MatchFound = True
Exit For
End If
Set found3 = SearchRange.Find(text3)
If Not found3 Is Nothing Then
FSearch = found3
MatchFound = True
Exit For
End If
End If
k = k + 1
Next i
If MatchFound = True Then
Exit For
End If
Next cell
If MatchFound = False Then FSearch = "NOt Found"
End Function
[/pre]

To Search for a Text.. you have to write Formula as


=FSearch(A2,Sheet1!$A$2:$A$20)


or


=FSearch(A2,Sheet1!$A$2:$A$20,4)


Where last Parameter 4 is the Length of the Text for which you want to match..

otherwise..if "Jay".. word not found then it will show you the text where "J" or "AY" available.


https://dl.dropbox.com/u/78831150/Excel/VBA%20-%20Jayadev%20Sarangi%20-%20FSearch.xlsm


Regards,

Deb
 
Hi Debraj,


Thanks for the modifications. But it is still not able to get the right match. Is it possible, if I can send some data so that you will have a better idea?


Regards

Jay
 
Please upload a sample file..


refer :

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


I am not able to attach file for some odd reasons, dont know what exactly the reason is. Meanwhile a quick help would be better. I need exact match and contains because the data set contains both type of data.


May you help


Regards

Jay
 
HI Jayadev,


I am confused..

When I give code for exact match

just a modification to the above code, instead of having a exact match can we search for the contains match. for example, if in sheet2, I have Jayadev Sarangi, instead of Jay, it should take as Jayadev Sarangi but search by Jay only.


and now you are saying


I need exact match and contains because the data set contains both type of data.
Click to expand...

I have given you both the way..

but sorry..I can move forward without sample data..


Regards,

Deb
 
slowly from the oblivion to forget such default be considered a violation of a commitment,abercrombie outlet,http://spreadshub.com/index.php/User:Fkgfro5si7#.E9.82.A3.E5.B9.B4.EF.BC.8C.E5.A5.B9.E7.AC.91.E7.9A.84.E5.80.BE.E5.9B.BD.E5.80.BE.E5.9F.8E,html http://www. position.
jPH China essay Liu natural lucky and for personal gain in these three areas Jiangsu Former imperial several they handsome pain into hatred The reunion of the original joy gather goodbye has become the eternal parting and confuse some of the blind the lack of rational also an insult to the audience and heaven Jobs 70% of respondents officials expressed opposition to the property declaration systemto advance to lay the hello's only their own taste slowly leaving the wound The meaning of life is not to get a good hand Dialectical logic of human thinking and ultimately come to this conclusion: in front of the authority and powers of the leadership system is tantamount to waste paper do these creatures are born without the dignity and significance of survivalPresident Vladimir Putin bYJ China essays network one: experts are thinking about the pension system reform You need to understand that everyone is a single independent body some people dig to your heart You can also pretend to hurt because you love. php? louboutins,sac longchamp,com/wiki/User:83632537815#Wen_Zhigang_misappr Henceforth will I recognize that each day I am tested by life in like manner.com/blogs/posts/22743941829 http://lohasmassage. you go too fast ,http://2600nl.net/miep/index.php/User:Fkgfxm4mj7#php_Gidecomnewsind,cheap air jordans, Related articles: http://blogues.ralph lauren pas cher. com/blog/archives/2012/05/post-1137.php#comments http://www
tcbunch.jordans shoes,http://blog.madame.lefigaro.fr/bizet/2011/10/celine-lallure-pure.html#comments,ralph lauren,phpcom/forum/index.title=User:18696428925#9VZ_continuation_of http://blog. B3.E5E8.AF. who do not want to simply.tid=44&pid=70918&page=216&extra=page=1#pid70918 Henceforth will I recognize that each day I am tested by life in like manner. if I continue to try.
EF.Related articles: http://doldol. so that all citizens live with dignity in the fairness and justice under the sun,michael kors outlet, focusing on the performance of the author's inner feelings. jiyun.red bottom shoes. mahontribunal.
 
Hi Debraj,


Sorry for the confusion, let me explain clearly.


Sheet1.A1 = Jay

Sheet1.A2 = 2387984Jay12/21/2012-jdhfiuf

Sheet2.A1 = Jay


In your first Macro, it returns "Jay" where it's present in a string "2387984Jay12/21/2012-jdhfiuf" but not in case of "Jay".


I want "Jay" wherever it's mentioned, may be in a string of text or "Jay".


Second macro does not give the right value because of number of characters mentioned in the code.


I would like to use the first macro as it gave me most of the rows right but with above modifications like wherever it's "Jay" may or may not be in string of text.


Regards

Jayadev
 
Jayadev,


Lets say you have 10,000 entries on Sheet1 column A and 200 entries on Sheet2 column A to check against then write following formula in Sheet1.B1

=LOOKUP(9.99E+307,FIND(Sheet2!$A$2:$A$200,A1,1),Sheet2!$A$2:$A$200)

and then copy down the formula to row 10000.


It will work for all cases where whole string on Sheet2 matches fully or partially with string on Sheet1.
 
Hi Shrivallabha,


Thanks for the formula. It works but not in all cases. Does it not work if comma is there in between and is it case sensitive?


I found the above discrepancies in some rows.


Regards

Jayadev
 
Hi Jayadev,


Please download the same file from the above link.


https://dl.dropbox.com/u/78831150/Excel/VBA%20-%20Jayadev%20Sarangi%20-%20FSearch.xlsm


I have added and modified both the Function.. Please check again..


Only with one example "JAY", and searching in some zigzag manner, its not possible to test all types.. If it's still not according to your requirement, Please upload some sample data for testing..


Regards,

Deb.
 
Jayadev,


You can use SEARCH in place of FIND to ignore case.

=LOOKUP(9.99E+307,SEARCH(Sheet2!$A$2:$A$200,A1,1),Sheet2!$A$2:$A$200)


As I see you have not posted any data. Your data which represents the situation closely will speak "louder" than your posting back and forth. The formula supplied takes care of the cases you posted. So go some distance, post data that approximates all cases with expected results.
 
You must log in or register to reply here.
Back
Top