+ Reply to Thread
Results 1 to 12 of 12

Match partial text when partial text is not exact

  1. #1
    Registered User
    Join Date
    02-28-2014
    Location
    Aurora, Colorado
    MS-Off Ver
    Excel 2007
    Posts
    37

    Match partial text when partial text is not exact

    I hope this is the right thread for this question. This is for Excel 2007, and would be ever so helpful. Please let me know if clarification is needed for anything.

    I have two sets of model numbers. One set is the full model numbers of the units we use, and the other is an abbreviated form used to lookup up certificate numbers. I need a way to match these up so I can use one set of search criteria to find out if there is a match. Here is an example of what I need to match with a partial text match:

    H,AE35(6,9)36+TD and AE3563636D145C2501AP

    H,RE36(6,9)36 and RE36936C145B2505AP

    Can this be done? It would be helpful if I could do this with a formula that matches multiple items at the same time (ie; if A & B & C match=true) with the above model number being one of those items (certificate numbers are issued for sets, but the other model numbers are fine).

    If that's not possible, a one time VBA run to match all of the abbreviations at once would also work. If these items are matched up with a one time VBA, the VBA needs to account for their being more than one match for each abbreviation depending on the size of the unit.

    So H,AE35(6,9)36+TD could be matched to:

    AE3563636D145C2501AP
    AE3563636D175C2501AP
    AE3563636D210C2501AP


    Is this possible?

    Thank you!

  2. #2
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Match partial text when partial text is not exact

    can you please upload a sample book.
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST

    More we learn about excel, more it shows us, how less we know about it.

    for chemistry
    https://www.youtube.com/c/chemistrybyshivaansh

  3. #3
    Registered User
    Join Date
    02-28-2014
    Location
    Aurora, Colorado
    MS-Off Ver
    Excel 2007
    Posts
    37

    Re: Match partial text when partial text is not exact

    Can you tell me if it's possible to do this without a sample book? I don't have one handy. I would like to know if it's possible to get a partial text match off of what is given. The abbreviated version of the model number is from an outside source that I have no control over. If you really need a sample book, I can see about putting one together.

  4. #4
    Registered User
    Join Date
    02-28-2014
    Location
    Aurora, Colorado
    MS-Off Ver
    Excel 2007
    Posts
    37

    Re: Match partial text when partial text is not exact

    Quote Originally Posted by NamiSama View Post
    Can you tell me if it's possible to do this without a sample book? I don't have one handy. I would like to know if it's possible to get a partial text match off of what is given. The abbreviated version of the model number is from an outside source that I have no control over. If you really need a sample book, I can see about putting one together.
    Also, any sample workbook I put together would be in Excel 2003. The workbook this is for is on a separate computer without access to the internet, and has Excel 2007.

  5. #5
    Valued Forum Contributor Saarang84's Avatar
    Join Date
    02-19-2009
    Location
    Chennai, India
    MS-Off Ver
    XL 2003 to 2010
    Posts
    812

    Re: Match partial text when partial text is not exact

    Quote Originally Posted by NamiSama View Post
    It would be helpful if I could do this with a formula that matches multiple items at the same time (ie; if A & B & C match=true) with the above model number being one of those items (certificate numbers are issued for sets, but the other model numbers are fine).
    Could you post a sample workbook which lists all different model number combinations that you use? Also include all related columns as well (Excel 2007 version is preferable)

    From your explanation above, I hope maybe we can try to derive a logic to generate the abbreviated form of the model number from the full form. Once the logic is available, then, we may try coding a formula using Index and Match to arrive at a solution for your requirement.
    Last edited by Saarang84; 05-21-2014 at 03:04 AM.
    If my assistance has helped, there is a reputation icon * on the left hand corner below the post - you can show your appreciation to the user who has helped in resolving your requirement.

    If your requirement has been solved please mark your thread as Solved.
    In the menu bar above the very first post, select Thread Tools, then select "Mark this thread as Solved".

    Kindly use [FORMULA] or [CODE] tags when posting your code.

    Regards,
    Sarang

  6. #6
    Registered User
    Join Date
    02-28-2014
    Location
    Aurora, Colorado
    MS-Off Ver
    Excel 2007
    Posts
    37

    Re: Match partial text when partial text is not exact

    Since I was promted twice for a sample book, I started to put one together. The items came together slightly differently than the original workbook because I was simplifying it. Unexpectedly, I found that my code already works! I think I had been working with these abbreviations for so long (and frustrated the whole time) I had convinced myself the problem was more complicated than it was. I would love to post the sample book I made, but I'm not sure how. Hopefully this thread will help others, as so many threads on here have helped me!

  7. #7
    Valued Forum Contributor Saarang84's Avatar
    Join Date
    02-19-2009
    Location
    Chennai, India
    MS-Off Ver
    XL 2003 to 2010
    Posts
    812

    Re: Match partial text when partial text is not exact

    Hi,

    Please feel free to post the sample workbook, I would like to know what you did.. Also, I can suggest a better solution if required.

  8. #8
    Registered User
    Join Date
    02-28-2014
    Location
    Aurora, Colorado
    MS-Off Ver
    Excel 2007
    Posts
    37

    Re: Match partial text when partial text is not exact

    Can you tell me how to attach the workbook?

  9. #9
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,627

    Re: Match partial text when partial text is not exact

    Down to the right you will find three buttons, click on the one in the middle that says "Go Advanced".
    On the new page, about half way down you will find a button that says "Manage Aattachments". Click on that.
    In the new window, click on "Choose file", then "Upload" and then "Close Window". Then click on "Submit Reply".
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.

  10. #10
    Registered User
    Join Date
    02-28-2014
    Location
    Aurora, Colorado
    MS-Off Ver
    Excel 2007
    Posts
    37

    Re: Match partial text when partial text is not exact

    Here is the example book I made for this thread.
    Attached Files Attached Files

  11. #11
    Valued Forum Contributor Saarang84's Avatar
    Join Date
    02-19-2009
    Location
    Chennai, India
    MS-Off Ver
    XL 2003 to 2010
    Posts
    812

    Re: Match partial text when partial text is not exact

    Quote Originally Posted by NamiSama View Post
    Here is the example book I made for this thread.
    Hi,

    Can you briefly explain what your requirement was and how did you arrive at a solution? Just looking at the workbook doesn't explain what you have done.

  12. #12
    Registered User
    Join Date
    02-28-2014
    Location
    Aurora, Colorado
    MS-Off Ver
    Excel 2007
    Posts
    37

    Re: Match partial text when partial text is not exact

    Quote Originally Posted by Saarang84 View Post
    Hi,

    Can you briefly explain what your requirement was and how did you arrive at a solution? Just looking at the workbook doesn't explain what you have done.
    My requirements were described in the first post of this thread. I will summarize from there:

    I have two sets of model numbers. One set is the full model numbers of the units we use, and the other is an abbreviated form used to lookup up certificate numbers. I need a way to match these up so I can use one set of search criteria to find out if there is a match. I also need to do this with a formula that matches multiple items at the same time (ie; if A & B & C match=true) with the above model number being one of those items (certificate numbers are issued for sets, but the other model numbers are fine).

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Highlight Exact and Partial Text, 2 Sheets
    By TPA Skooly in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-11-2013, 03:50 PM
  2. Vlookup with only partial text match
    By betsy2128 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-18-2013, 08:03 AM
  3. vloopup with partial text match
    By piedimonster in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-06-2013, 02:02 PM
  4. matching partial text within one cell to partial text within another
    By Solstice in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 03-03-2010, 09:13 PM
  5. Sort with Partial Text Match
    By kfenolio in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-18-2008, 10:26 PM

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