Search cells against a list of aliases in Excel 1

[gabber4858]

I am trying to normalize entries in an Excel 2007 spreadsheet to eventually import into Access (also 2007). The problem I am running into, is previous users choose to abbreviate entries differently. Is there a way to "search("*value*",....)" against a table of known aliases and have the result paste in the new value? I am currently using the search function, but I have to make multiple columns to get the result I want. I could use this on a people's names too.

An example would be:

Name(cell A1) New Name (cell A2):
Meeting Rm Conference Room
Conference Rm ""
Conf Rm ""
Con Room ""
Mtg Rm ""


I do not know VB, so a formula would be ideal if possible, or a really good example Thank you in advance!

 

[Welshbird]

I think it this is a one off I'd be tempted to just do many 'find replace' and just do it the once...

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[lionelhill]

I might even be tempted to sort the data by the offending column so that like selections will often fall together, and then manually copy an appropriate name into the whole block of cells.

If you come up with a way to deal with people's names, you will have done a heroic service. It is a fundamental feature of anything involving names that the number of names in a list will generally exceed the underlying number of people by a factor of somewhere between 1.2 and 5!

 

[SkipVought]

hi,

Data cleanup is an arduous task. There is no 'silver bullet.'

I am currently working a project that has a similar requirement. AS a result, I built a Function, (refered to as a user defined function or UFD) which looks for LIKE matches.

Before you go substituting your strings in this, you must do some research into your data, using the AutoFilter -CONTAINS criteria, to be absolutely sure that your PROPOSED search string behaves as you expect.

If you have new data coming into your table, be aware that your current assumptions may not be all encompassing as these changes occur.

PASTE this function into a MODULE in the VB Editor and use it like any other spreadsheet function...

Function NomCat(NOMEN As String)
    'make the criteria UPPER CASE
    NOMEN = UCase(NOMEN)
    
    If NOMEN Like "*MEET*" Or _
        NOMEN Like "*MTG*" Or _
        NOMEN Like "*CON*" Then
        
        NomCat = "Conference Room"
        
    ElseIf NOMEN Like "*BATH*" Or _
        NOMEN Like "*LADI*" Or _
        NOMEN Like "*MENS*" Or _
        NOMEN Like "*REST*" Then
        
        NomCat = "Rest Room"
    Else
        NomCat = "Other"
    End If
End Function

Notice that I amde an assumption that *CON* will work in your table. However, when you TEST con in the Auto Filter CONTAINS criteria, you may get results that are not correct. Modify the code accordingly.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[gabber4858]

WOW! That is really cool! I will play with this some more, but I think this will help for what I am doing. I see how this would be difficult for names, it make for a very long list. I was hoping to make a range of aliases and point to it, but for now, this is way better than stare and compare! Thank you!!!!!