Function to extract values

[Rashid Khan]

Hello All Experts,
I am using Office XP and have the following problem:

I have data in Col AA for examples as follows:

AA1 =Text 1 = eg John
AA2 =Text 2 = eg Johnny
AA3 =Text 3 = eg James
AA4 = Text 4 = eg Henry
AA5 = Text 5 = eg George
.....
.....
a range of unique Text Strings in AA1:AA25

I wish to select the Col A and run the macro. The macro should look up the
value in the above range in Col AA and in corresponding ColB it should have
the value extracted.
for eg.
A1 = Abraham John Joseph -- then B2 should have the value John only (as it
is in the range in Col AA)
A2 = Henry the II -- then B2 should have the value Henry only
A3 = The Junior George -- then B2 will have the value George only

In other words Col B should have atleast one value from the String in AA1 if
found otherwise it should be blank
Pls note that no two words are repeated in the String in Col A for eg..
there are no two Johns no two George and so on

Can this be achieved thru VBA or a formula. Any suggestions are requrested

Many thanks in advance
Rashid Khan

 

[Rashid Khan]

Hi Mark,
Thanks for your reply.
I am answering here:

I guess the question is whether the name from column AA has to be an exact
full-word match of one of the individual names (i.e. first, middle, last)
within a name in column A. In other words, if column A has "Danielle Smith",
and column AA has "Daniel", is that considered a match? Or does the name in
AA have to be "Danielle"?

No it has to just match eg Danielle Smith... in AA then Danielle or Daniel
would do. However I do not have any such strings in AA.

If you don't need a full-word match, you can just use the InStr function to
tell whether a name from AA is a substring of a name from column A. You'll
do a nested loop: outer loop iterates through each name in column A. Inner
loop iterates through each name in column AA, looking for a match (where the
InStr function returns a value greater than zero). When a match is found,
the name from column AA is copied to column B.

Yes this is what would suffice for my requirement. As far as writing the
code ... I am a very very new to macro programming. This NG has been a
tremendous help for me and I am sorry that I do not know how to code what u
have explained.

Rashid Khan

 

[Mark Thorpe]

Hi Rashid -

This code should work for you. I tried to put in plenty of comments to help
you learn more about Excel VBA:

Sub SearchNames()

Dim lRowCount As Long
Dim lRow As Long
Dim iRowAA As Integer
Dim sFullName As String
Dim sMatch As String ' match found in column AA

' Count number of used rows in the sheet:
lRowCount = ActiveSheet.UsedRange.Rows.Count

' Outer loop: look at each name in column A:
For lRow = 1 To lRowCount
sFullName = Cells(lRow, 1).Value
iRowAA = 1
sMatch = ""

' Inner loop: look at each name in column AA
' until either a match is found, or run out
' of names:
While Cells(iRowAA, 27).Value <> "" And sMatch = ""
' InStr function returns value greater than
' zero if string2 is found within string1:
If InStr(sFullName, Cells(iRowAA, 27).Value) > 0 Then
sMatch = Cells(iRowAA, 27).Value
End If
iRowAA = iRowAA + 1
Wend

' Place the match in column B:
Cells(lRow, 2).Value = sMatch
Next lRow


End Sub

Also, you might take a look at a series of articles called "Excel VBA: No
Experience Necessary" on TechTrax online magazine. I'm not trying to
advertise, but you might find them helpful.
http://pubs.logicalexpressions.com/Pub0009/LPMIssue.asp?ISI=0

Mark

 

[Rashid Khan]

Hi Mark,
Wow... U r a real magician... The macro ran on 46000 rows in 6 minutes ...
Thanks a lot..
I may have some practical problems after running this macro..but I will have
to see what it is... I will post back if any further help is required.

Thanks a lot for all your help and the wonderful URL u have sent. I will
sure go thru it.


Rashid Khan

 

[Rashid Khan]

Hi Mark,
There is a slight problem with this macro.. It is finding everything with
the InStr Function...
I need to find the string from within a string but it should from the
beginning of a word.. maybe the macro can search a space also.

Let me give u an example
My list has Mahad
so it is displaying from Gurumahadeo as Mahad... note that mahad is within
this word Gurumahadeo.
The example u quoted earlier about Daniel and Danielle was ok with me as
long as it would extract the whole word from the beginning.. this macro
would extract Dan, Dani and Niel also.

How can we rectify this problem?
Any suggestion would be appreciated.
Rashid Khan

 

[Mark Thorpe]

Hi Rashid -

You should be able to check to see if the substring is at the beginning of
the longer string (InStr will return 1), and, if not, check to see if it
appears preceded by a space:

If InStr(sFullName, Cells(iRowAA, 27).Value) = 1 Then ' occurs
at beginning
sMatch = Cells(iRowAA, 27).Value
ElseIf InStr(sFullName, " " & Cells(iRowAA, 27).Value) > 0 Then
' occurs preceded by space
sMatch = Cells(iRowAA, 27).Value
End If

 

[Rashid Khan]

Hi Mark,
Pardon me for my ignorance. But the suggestion u sent does not get the
result what I want.
Let me explain to u further.
The strings in Col AA appear either at the beginning in Col A or in between
somewhere always preceded by a space in Col A.

The suggestion u posted searches for either in the beginning with the value
= 1 but the > 0 parts extracts part of the string as I already mentioned in
my previous post.

Hope u get it now
Rashid

 

[Mark Thorpe]

Note that in the line:

ElseIf InStr(sFullName, " " & Cells(iRowAA, 27).Value) > 0

we are searching for the value from column AA preceded by a space. (thus the
" " &)

For example, it will not find "mahad" in "Gurumahadeo", because it first
checks to see whether Gurumahadeo begins with mahad (does not), then
searches for " mahad", which it does not find because of the space added to
the front.

 

[Rashid Khan]

Hello Mark,
Sorry once again for the confusion on my part.. The String is Mahadeoguru ..
thats why it is extracting Mahad from it.. as it is preceded by a space.

Can this u give u some idea.
Rashid

 

[keepITcool]

Rashid,
i think you can forget the VBA..

since you're NOT requiring an exact match
THIS simple function should do..

=SUMPRODUCT(--(NOT(ISERROR(FIND($AA$1:$AA$25;W1)))))

if it's 0 => no occurance..
if higher the number of matches found

keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Rashid Khan]

Hi there,
I copied the formula u suggested in B1.. It gives an error :-(
By the way what is the W1 in the right hand side of the formula?

Rashid

 

[keepITcool]

w1 is the cell reference for the text that needs to be looked up..
you just may have to change that to a1?


the error could very well be caused by the fact that i used the
; as list separator... maybe you use a , instead ?



keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Rashid Khan]

Hi there,
Ok ...Thanks. Your formula works showing me 0 and 1.. But how is that
supposed to help my problem

Rashid Khan