+ Reply to Thread
Results 1 to 8 of 8

Pulling data from a list by matching partial criteria

  1. #1
    Registered User
    Join Date
    07-24-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    3

    Red face Pulling data from a list by matching partial criteria

    Hi i'm new to this forum, so apologies if I post anything incorrect. I'm sure I'll learn fast.

    I'm trying to retrieve data based on a string of letter in a name which match what I select in a cell coloured yellow.

    Example, people enter names slightly differently and spelling mistakes can occur. Where a name is called "Steven Tyler", I want to find and retrieve all instances where my criteria in Cell "C2" is a partial name anywhere within the name field example:- "Tyle" or "even" or "teve". I can produce the retrieve to work on exact match, but i'm struggling do solve the partial.

    Any help or advice would be so warmly received.

    Spread sheet example attached
    Attached Files Attached Files

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    21,290

    Re: Pulling data from a list by matching partial criteria

    Welcome to the Forum sit75!
    Which direction are you trying to go? Do you want to enter "Steven" in C2 and then find all names that contain that string anywhere? If so, then you're also going to match "Steven Hawkins" which seems to be a completely different person, so it's overcorrecting. Or do you want to type "Steven Tyler" in C2 and find all names that have a first name of "Steven"? If so, you will match Steven Tiler but you will also match Steven Hawkins. As with many Excel questions, the hard part is clearly defining what your requirements are.

    If you want to do simple checks, Excel can do the matching. My strategy would be to add an extra column on your Data sheet with a formula to do that match, then on Output select the rows based on the result in that column. However, this problem gets big depending on how complicated you want the matching to be. At some point you cross over into needing a solution using VBA macros.
    Jeff
    | | |?| |?| |?| |?| | |:| | |?| |?|
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    07-24-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Pulling data from a list by matching partial criteria

    Jeff,

    Thanks you for your warm welcome and reply.

    Im not looking to match the full name as it does this already, however when spelling errors occur this makes me question the output data being correct. So I want to search in the yellow coloured cell and type eg. "teve" which would find all Stevens regardless of the surname and if Steven had been spelled incorrectly.

    When you say VBA macros, I thought due to the complex nature this may be route to go down, but I know nothing about them yet.

  4. #4
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,445

    Re: Pulling data from a list by matching partial criteria

    You can a wild card to you search of column A, but "teve" could return any name with the four letters on it.

    Please Login or Register  to view this content.
    You can remove one of wild cards if that gives you a more accurate match.
    Even VBA macro works similar to wild card, you can have either a whole match, or partial match, yes you can add case sensitivity to the match, but I doubt if VBA will find you your ideal solution.

  5. #5
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    21,290

    Re: Pulling data from a list by matching partial criteria

    Here is a solution using your file. This approach is a little different than what AB33 suggests although that approach is perfectly valid.

    This approach makes three changes:
    1. It adds an extra column on Data to indicate whether that name is a match for the name entered on Output. This formula uses the FIND function to see if the string appears in the name.
    2. The Key formula references the value in this new column, rather than doing the test for a match itself.
    3. The Count value on Output counts the number of hits shown in the new column on Data.

    Note that the match is insensitive to case. So "Teve" will match "Ferd Teves" and also "Steven Tyler".

    If you had given me a more complex rule, like "The string must match any name that contains that string but the match is allowed to be different by one letter" then we would be talking VBA.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    07-24-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Pulling data from a list by matching partial criteria

    Thank you both (AB33 & 6StringJazzer), they are just what I wanted.

    im delighted

    cheers

  7. #7
    Registered User
    Join Date
    10-15-2014
    Location
    Mumbai
    MS-Off Ver
    2010
    Posts
    23

    Post Re: Pulling data from a list by matching partial criteria

    Hi

    Thanks for sharing this vital information. I am working on a sheet where the names of the customers are not in proper order. To make it clear further the name David, Lee can be used Lee, David in the sheet. Could you please help me with this.

    Regards,
    Jagdev

  8. #8
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    21,290

    Re: Pulling data from a list by matching partial criteria

    Jagdev, please take the time to review our rules. There aren't many, and they are all important.

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

+ 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. Replies: 4
    Last Post: 06-08-2013, 01:52 PM
  2. Need help pulling information from a cell, based on two matching criteria
    By acvitanov in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-18-2012, 06:45 PM
  3. [SOLVED] Pulling Multiple Results by Matching one variable from List of Variables
    By caitlinkeats in forum Excel General
    Replies: 5
    Last Post: 04-09-2012, 07:24 PM
  4. Replies: 1
    Last Post: 07-14-2010, 07:08 PM
  5. Multiple Criteria and Partial Date Matching
    By GreatLakesJK in forum Excel General
    Replies: 7
    Last Post: 07-08-2010, 11:03 AM

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