Page 2 of 5 FirstFirst 1 2 3 4 ... LastLast
Results 11 to 20 of 47

Thread: vLookup with wildcards (equivalent of "contains") - best solution?

  1. #11


    Register for a FREE account, and/
    or Log in to avoid these ads!

    Could be in any order - these are search terms 'in the wild', so could be ANYTHING! I just want to find the ones that refer to actual authors, not generic subject-related terms.

    Edit: also compared to your example, there may be many, many entries for each author, and I'd want to find all of them.

    Is it possible to do this with a different formula?
    Last edited by FlossieT; 2011-07-12 at 05:38 PM.

  2. #12
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,344
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Hmmm... In that case, to be honest, I'd probably do one of the following:
    1) Flip the logic so that you're looking up authors against your list of searc terms, or
    2) Write VBA code to check for matches

    If you can do the first, I would. The second is going to take more time and it's less likely to be maintainable depending on the skillsets of the users involved...
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #13
    I'll have a go at flipping, but I'm not sure I can quite get my head around it... thanks for all your help, though - very much appreciated. Don't want to go down the VBA route if I can help it!!

  4. #14
    The problem is, I need to return multiple results for each author name, and I also need to record those results against the search terms for further analysis. I don't think this is going to work...

  5. #15
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,344
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    You already have a table of authors, correct? Just run the formula down the side of that table instead of the search terms and I'd think you'd be good. (If you feel you can share your data, you could always upload your workbook so we could help work on it.)
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  6. #16
    I can't share the original data, unfortunately, but I'll try and put something together that shows the problem... give me a minute!

  7. #17
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,344
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    I'm going to give this one some thought today and will loop back on it later.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  8. #18
    OK - in case it helps, I've attached a mockup of what I'm trying to do (with a very small dataset - the one I'm actually working with is several thousand rows); the 'desired result' tab shows what should be happening with the author flagging (but I can't get to work...).

    author_names.xlsx

  9. #19
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,344
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    I'm not going to say that this can't be done with a formula, but I haven't been successful in working it out. I can do it with VBA though using the following UDF:

    Code:
    Function COUNTLIKE(arg As Range, pattern As String) As Long
        Dim cl As Range
        For Each cl In arg
            If LCase(pattern) Like "*" & LCase(cl.Value) & "*" Then COUNTLIKE = COUNTLIKE + 1
        Next cl
    End Function
    I've attached a copy of the workbook with the UDF in use. If you'd like to go with it, let me know and I can help you out with the steps to put it in place.
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  10. #20
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    401
    Articles
    0
    Excel Version
    Excel 2016
    Quote Originally Posted by Ken Puls View Post
    I'm not going to say that this can't be done with a formula, but I haven't been successful in working it out. I can do it with VBA though using the following UDF:

    Code:
    Function COUNTLIKE(arg As Range, pattern As String) As Long
        Dim cl As Range
        For Each cl In arg
            If LCase(pattern) Like "*" & LCase(cl.Value) & "*" Then COUNTLIKE = COUNTLIKE + 1
        Next cl
    End Function
    I've attached a copy of the workbook with the UDF in use. If you'd like to go with it, let me know and I can help you out with the steps to put it in place.
    Bob may disagree Ken, give him a nudge on this one as he loves a challenge, in fact i believe he created a formula for me on a similar subject but using sumproduct =SUMPRODUCT(--(LEFT(Sheet1!$A$1:$A$10&" ",FIND(" ",A1&" "))=LEFT(A1&" ",FIND(" ",A1&" "))),Sheet1!$B$1:$B$10) you may find this thread interesting http://www.vbaexpress.com/forum/showthread.php?t=33410
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

Page 2 of 5 FirstFirst 1 2 3 4 ... LastLast

Tags for this Thread

Posting Permissions

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