Vlookup, Find or VBA

Posted by Darren on August 15, 2001 10:58 PM

Assistance with the following problem would be appreciated.

I have two worksheets one has an person id code in one column and a surname in the second column. The second worksheet contains a column of full name, some of these contain initials.

I would like to be able to take surname and where surname is found as part of full name insert person id into an adjacent column.

If not this then seperate all the other information out of full name and insert just the surname portion into a adjoining column.

There is likely to be many duplicates of surname, I can work on this later.

Posted by Aladin Akyurek on August 16, 2001 2:21 AM

Darren,

Ok, you asked for it. ;)

I'll assume the following sample data in your first worksheet.

{"id","surname";2,"zax";3,"doe";4,"xon";5,"right";6,"olivier";7,"denton"}

This lump of data is in A1:B7.

Select A2:A7 and name this range IDCODES via the Name Box.
Select B2:B7 and name this range SURNAMES.

I'll assume the following sample data in your second worksheet:

{"surname";"gerard zax";"alex denton";"a. b. xon";"john doe";"d. numeric";"joe dalton";"aladin akyurek";"damon ostrander"}

This lump is in A1:A9 [ I reckon Damon wouldn't mind. ]

Important Caveat. As you can see, I assume the surname to be the last part of a full name, with space as delimiter.

In A2 enter: =IF(ISNUMBER(MATCH(reversetext(LEFT(reversetext(A2),SEARCH(" ",reversetext(A2))-1)),SURNAMES,0)),INDEX(IDCODES,MATCH(reversetext(LEFT(reversetext(A2),SEARCH(" ",reversetext(A2))-1)),SURNAMES,0)),reversetext(LEFT(reversetext(A2),SEARCH(" ",reversetext(A2))-1)))

Copy down this mega-formula as far as needed.

REVERSETEXT is a user-defined function (UDF) which is due to Walkenbach.
If you don't know how to add a UDF to a workbook, just say so [ It's easy. Even I learned how to do it ].

Option Explicit

Function REVERSETEXT(text) As String
'
' Returns its argument, reversed
' J. Walkenbach
'
Dim TextLen As Integer
Dim i As Integer
TextLen = Len(text)
For i = TextLen To 1 Step -1
REVERSETEXT = REVERSETEXT & Mid(text, i, 1)
Next i
End Function

I used this UDF to have a hassle-free way extracting the surname from a full name the way I assumed it's structured.

Aladin

Posted by Darren on August 16, 2001 9:17 PM

Thanks Aladin, I'm deciphering now ;)

Returns its argument, reversed J. Walkenbach