Smart Lookup

[mrroland]

Hi,

I don't know if this is possible in excel but I will give it a try. Just have a look at this. Column A and B are from a clean source, Column C is the output that I am looking for. Problem is with the data that has arrived that there is a great variation in the way that the models are described. A simple index with match function as seen in cell G3 doesn't work. Basically I need a lookup that will only look at the letters and doesn't look at spaces or digits. The combination of letters is variable and can include all letters from A to Z

Book1.xls
	A	B	C	D	E	F	G	H
1	ThisistheCleanSource			0	0	0	Output	0
2	Equipment	Model	Configuration		Equipment	Model	Configuration	0
3	IMODELAVAL	TVFS	RotaryScrew		IMODELAVAL	TVFS	RotaryScrew	0
4	0	0	0	0	IMODELAVAL	TVFS200		0
5	0	0	0	0	IMODELAVAL	4TVFS200		0
6	0	0	0	0	IMODELAVAL	4TVFS200		0
7	0	0	0	0	0	0	0	0
Sheet1

I hope this makes sense and you lot can help me here,
Cheers,
Roland

 

[WillR]

This may indeed be possible with a native excel function.... but for now, using the following UDF, say in column G, and using the results for your INDEX MATCH would appear to work for my small test.

It converted data such as

TVFS 200
TVFS 201
TVFS 202
TVFS 203
4TVFS 204
TVFS 205
TVFS


to this

TVFS
TVFS
TVFS
TVFS
TVFS
TVFS
TVFS

and I think that was what you were getting at (?)

Here is the FUNCTION code... you can place it in a standard module

Function StripTheLetters(stdText)
'strips the Letters from a text string containing numbers
Dim strLetters As String

stdText = Trim(stdText)
strLetters = ""
For i = 1 To Len(stdText)
    If IsNumeric(Mid(stdText, i, 1)) = False Then
        strLetters = strLetters & Mid(stdText, i, 1)
    End If
Next i
StripTheLetters = strLetters
End Function

I am sure someone else will probably improve on my clunky workaround hope it helps anyhow

 

[fairwinds]

Hi,

I did a formula aproach but as it is a little complex, maybe WillR's suggestion is to prefere.

I post it anyway so you can choose.

Book1
	A	B	C	D	E	F	G	H	I
1
2	IMODELAVAL	TVFS	Rotaryscrew	IMODELAVAL	4TVFS200	4	200	TVFS	Rotaryscrew
3	IMODELAVAL	TVSF	Somethingelse	IMODELAVAL	TVFS200	#N/A	200	TVFS	Rotaryscrew
4	IMODELAVAL	TFVS	Yetanotherthing	IMODELAVAL	TFVS	#N/A	#N/A	TFVS	Yetanotherthing
5				IMODELAVAL	555TVSF444	555	444	TVSF	Somethingelse
6				IMODELAVAL	5TVSF5	5	5	TVSF	Somethingelse
7				IMODELAVAL	555TVSF	555	#N/A	TVSF	Somethingelse
8				IMODELAVAL	TVSF447	#N/A	447	TVSF	Somethingelse
9
10
Sheet1

 

[mrroland]

WillR,

Thanks for your reply. The output of your FUNCTION is certainly what I am looking for . The only problem is my knowledge of VBA. if i past your code into a standard module it doesn't seem to work for me. I moved the output configuration column to column H. but if I do run macro it says: compile error. expected end sub. Is the function not a macro? What does UDF mean?

What am i doing wrong.

Cheers

 

[Travis]

UDF stand for "User Defined Function" and it works like a formula not a macro

If you have pasted it in a standard module you should be able to use

=StripTheLetters(A1)

to remove the numbers and spaces from the string in A1
change to suit.

Edit:
If you pasted it in your personal.xls then you will need to do
=PERSONAL.XLS!StripTheLetters(A1)

 

[mrroland]

Will,

Thanks a million !!! It works like a dream.

Cheers,
Roland

 

[mrroland]

Will,

Actually there is still one thing that needs to be added to this UDF. The result of the function does return the exact thing i need but unfortunately it still includes spaces and the index match formula doesn't like that. Now I can get around this by adding another column and use a trim formula but I think the UDF can also do this. As I said my knowledge of VBA is nonexistend so I apologize for this simple request.

Cheers,
Roland

 

[fairwinds]

If you don't know how to do it in the code you could always:

=SUBSTITUTE(StripTheLetters(A1)," ","")

 

[WillR]

I added Trim() to the last line - it should now just return the letters.

Function StripTheLetters(stdText)
'strips the Letters from a text string containing numbers
Dim strLetters As String

stdText = Trim(stdText)
strLetters = ""
For i = 1 To Len(stdText)
    If IsNumeric(Mid(stdText, i, 1)) = False Then
        strLetters = strLetters & Mid(stdText, i, 1)
    End If
Next i
StripTheLetters = Trim(strLetters)
End Function

HTH