Results 1 to 9 of 9

Smart Lookup

This is a discussion on Smart Lookup within the Excel Questions forums, part of the Question Forums category; Hi, I don't know if this is possible in excel but I will give it a try. Just have a ...

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    Rijsbergen, Netherlands
    Posts
    294

    Default Smart Lookup

    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


    ******** ******************** ************************************************************************>
    Microsoft Excel - Book1.xls___Running: xl2000 : OS = Windows Windows 2000
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    E
    F
    G
    H
    1
    This*is*the*Clean*Source**000Output0
    2
    EquipmentModelConfiguration*EquipmentModelConfiguration0
    3
    IMO*DELAVALTVFSRotary*Screw*IMO*DELAVALTVFSRotary*Screw0
    4
    0000IMO*DELAVALTVFS*200*0
    5
    0000IMO*DELAVAL4TVFS200*0
    6
    0000IMO*DELAVAL4*TVFS*200*0
    7
    00000000
    Sheet1*

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


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

  2. #2
    Board Regular WillR's Avatar
    Join Date
    Feb 2002
    Location
    Sutton Coldfield
    Posts
    1,143

    Default Re: Smart Lookup

    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

    Code:
    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
    /**\ Regards, Will /**\

  3. #3
    MrExcel MVP fairwinds's Avatar
    Join Date
    May 2003
    Posts
    8,632

    Default Re: Smart Lookup

    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.

    ******** ******************** ************************************************************************>
    Microsoft Excel - Book1___Running: xl2000 : OS = Windows Windows 2000
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    E
    F
    G
    H
    I
    1
    *********
    2
    IMO*DELAVALTVFSRotary*screwIMO*DELAVAL4*TVFS*2004200TVFSRotary*screw
    3
    IMO*DELAVALTVSFSomething*elseIMO*DELAVALTVFS*200#N/A200TVFSRotary*screw
    4
    IMO*DELAVALTFVSYet*another*thingIMO*DELAVALTFVS#N/A#N/ATFVSYet*another*thing
    5
    ***IMO*DELAVAL555TVSF444555444TVSFSomething*else
    6
    ***IMO*DELAVAL5**TVSF***555TVSFSomething*else
    7
    ***IMO*DELAVAL555TVSF555#N/ATVSFSomething*else
    8
    ***IMO*DELAVALTVSF447#N/A447TVSFSomething*else
    9
    *********
    10
    *********
    Sheet1*

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
    "Fair Winds and Following Seas"

  4. #4
    Board Regular
    Join Date
    Apr 2002
    Location
    Rijsbergen, Netherlands
    Posts
    294

    Default Re: Smart Lookup

    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

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Phoenix, Arizona
    Posts
    1,711

    Default Re: Smart Lookup

    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)

  6. #6
    Board Regular
    Join Date
    Apr 2002
    Location
    Rijsbergen, Netherlands
    Posts
    294

    Default Re: Smart Lookup

    Will,

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

    Cheers,
    Roland

  7. #7
    Board Regular
    Join Date
    Apr 2002
    Location
    Rijsbergen, Netherlands
    Posts
    294

    Default Re: Smart Lookup

    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

  8. #8
    MrExcel MVP fairwinds's Avatar
    Join Date
    May 2003
    Posts
    8,632

    Default Re: Smart Lookup

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

    =SUBSTITUTE(StripTheLetters(A1)," ","")
    "Fair Winds and Following Seas"

  9. #9
    Board Regular WillR's Avatar
    Join Date
    Feb 2002
    Location
    Sutton Coldfield
    Posts
    1,143

    Default Re: Smart Lookup

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

    Code:
    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
    /**\ Regards, Will /**\

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com