+ Reply to Thread
Results 1 to 4 of 4

Partial Lookup Formula

  1. #1
    Registered User
    Join Date
    11-03-2010
    Location
    Iowa
    MS-Off Ver
    Excel 2003
    Posts
    2

    Partial Lookup Formula

    Hello everyone. I am new and came here looking for help on a problem I am having.

    I am looking for some sort of formula that will allow me to search a large spreadsheet for partial matches. I will be honest that I don't always understand how the formula works, so please be patient if I do not follow.

    I have a large spreadsheet that has my companies part numbers in column A. In the other columns I have different information such as description of part, vendors, those vendors part numbers, and part number cross references. I have information all the way out to column AD, and rows all the way down to 6300. From time to time we get new vendors that supply us with parts listings for what they carry.

    So I am looking for an easier way to cross reference our spreadsheet to see if we have our company's part number already set up for that part number or a cross reference. I can't do an exact match lookup because they may have added letters or numbers to the part, or they may have the part number all grouped together as one number and our spreadsheet has them with spaces or dashes.

    I realize that I may get some false matches since I would only be matching partial information, but even that would still allow me to narrow it down. Right now I am stuck switching back and forth between worksheets and just using the Find icon (Cntrl + F). But I would like to find a formula that will allow me to lookup the information from a cell on one worksheet, and if it finds it anywhere in the other worksheet it would return our part number from Cell A. Then I would be able to go through them and compare descriptions to see if they are truly the same part. My plan was to break down the part numbers from the new vendors sheet into separate cells. That way I could eliminate the added letters, dashes, or manufacturer down to individual columns.

    Is there something that will allow me to do this, or am I stuck using the Find function and going one by one?

    Any help would be appreciated.

    Thanks

    Shawn

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Partial Lookup Formula

    Perhaps in the original sheet, you can add a column that removes all spaces and dashes, and perhaps other characters with the use of some nested Substitute functions.

    So for example, say your parts are listed in column A and you enter a in say AE2:

    =Substitute(Substitute(A2," ",""),"-","") and copied down.

    Then you can use Data|Filter|Autofilter and in the AE column do a filter for "Contains" and enter the Part ID...

    You should get a filtered list that you can more easily go through and/or copy to another sheet.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    11-03-2010
    Location
    Iowa
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Partial Lookup Formula

    Well the issue there would be that a lot of the cells would have to have that done.

    Column A is our internal part number. Column B is our internal part description. For instance just making something up it might be Brg,flg,456712,ABB12,Equip123

    This could also be true of the different vendors and their part numbers we already have on the spread sheet. Some have even more info in the description, so it would require me to break down each cell into possibly 7 or 8 separate cells through probably 80 percent of the current columns.

    The new vendors parts listing may be in upwards of 1000 parts. So entering each on one at a time would be very time consuming as well.

    Thanks

    Shawn

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Partial Lookup Formula

    I am not sure of many other alternatives... you can have a look for fuzzy lookups in google to see some pretty complex vba codes... but if you want it to be easy you may need to do some extra work upfront to decode the columns a bit....

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1