1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel - Two lists, search for any string in list 2 in any entry in list 1

Discussion in 'Business Applications' started by dem0sthenes, May 11, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. dem0sthenes

    dem0sthenes Thread Starter

    Joined:
    Dec 15, 2009
    Messages:
    26
    I have two lists.
    List one is raw data. List two is a collection of flag/alert words.

    I need to find any term in list one that contains any flag word from list two.

    Examples
    LIST ONE
    appleworm
    wormapple
    woapplerm

    LIST TWO
    Apple

    In this example every term on list one would return a result of 'true'. LIST TWO is always much shorter than list one.

    I was trying to set it up so that in the column directly to the right of LIST ONE produces a 'true' if that list one term contains in any fashion any term from LIST TWO.

    As you will see in the example I was also trying to have a similar column to the right of LIST TWO so that I could easily see how many of the key terms had been found in the much larger list.

    The sample also has color fill based on the True/False to make it easier to visualize.

    Can anyone help make this work correctly?
    Match does not work because I am not looking for identical strings.
    I have not been able to get Find or Search to work either.

    As of right now it only seems to look at the other list's entry on the exact same ROW.
     

    Attached Files:

  2. Sponsor

  3. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    While Search could be used in a formula like this:
    =IF(ISERROR(SEARCH(B2,A:A)),"False","True")
    I would suggest you go with INDEX/MATCH like in the attached.
     

    Attached Files:

  4. dem0sthenes

    dem0sthenes Thread Starter

    Joined:
    Dec 15, 2009
    Messages:
    26
    When I apply the formula it only returns 'true' for the exact matches. I want it to return true for the entries in LIST ONE that even CONTAIN any entry in LIST TWO, not just the exact matches.

    This would mean that the entries (XAAA, XAAAX, AAAX, XBBB, XBBBX, BBBX) would all return true because they contain the AAA or BBB from LIST TWO.

    Is that possible?

    Thanks!
     
  5. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    It would be possible using VBA, but I can't figure out how to do it just using a formula - the best I can do is the attached.
     

    Attached Files:

  6. dem0sthenes

    dem0sthenes Thread Starter

    Joined:
    Dec 15, 2009
    Messages:
    26
    If there werent going to be that many entries for list two I would consider using that but there will be at least 50 and needing 50 columns, one for each, just wouldnt be feasible. I would like to have the results in the two columns initially shown. Would you know how to do that in VBA?
     
  7. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    I don't, but there are plenty of people here who can figure this out. Unfortunately, none of the ones I know are on the site at this time.... :(
     
  8. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    This would work for up to 30 values, but after that you can't nest any more If statements and would need a second test for the next 20 or so values.
    =IF(AND(ISERROR(SEARCH(D$2,A2)),ISERROR(SEARCH(D$3,A2)),ISERROR(SEARCH(D$4,A2))),"","True")
    (you would just keep adding a variable, so the next one to add would be ISERROR(SEARCH(D$5,A2)) )
     
  9. Keebellah

    Keebellah Hans Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,351
    I did some coding, two functions to use as formula.
    It has to be adapted if the check has to disregard the letter case,

    Add lcase() to both values if necessary
     

    Attached Files:

  10. Keebellah

    Keebellah Hans Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,351
    the code altered to check with all values as lcase (lowser case)

    Function InList1(tList2 As Variant) As String
    Dim xrow As Long
    If Len(Trim(tList2)) = 0 Then Exit Function
    For xrow = WorksheetFunction.Max(2, Range("A65536").End(xlUp).Row) To 2 Step -1
    If InStr(1, LCase(Cells(xrow, 1).Value), LCase(tList2)) > 0 Then
    InList1 = "true"
    Exit For
    End If
    Next xrow
    End Function

    Function InList2(tList1 As Variant) As String
    Dim xrow As Long
    If Len(Trim(tList1)) = 0 Then Exit Function
    For xrow = WorksheetFunction.Max(2, Range("D65536").End(xlUp).Row) To 2 Step -1
    If InStr(1, LCase(tList1), LCase(Cells(xrow, 4).Value)) > 0 Then
    InList2 = "true"
    Exit For
    End If
    Next xrow
    End Function
     
  11. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Thanks for coming Hans! :)
     
  12. Keebellah

    Keebellah Hans Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,351
    my pleasure :)
     
  13. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    I'd never heard of the InList function!
     
  14. Keebellah

    Keebellah Hans Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,351
    The Inlist function is the function I wrote in VBA, I thought that that way the name explains what it does.

    All the function does is check if the parameter value you send to it is present, and that I check with the Instr() function which is a builtin VBA function.
     
  15. dem0sthenes

    dem0sthenes Thread Starter

    Joined:
    Dec 15, 2009
    Messages:
    26
    Hey guys!

    This is working for me!

    The only problem that arises is that the column to the right of list 2 does not update itself if I remove an entry from list 1.

    I tried clicking the 'calculate sheet' button under the formulas tab but that does not do it.

    Ultimately it is not really an issue as I can just save/close the book and re-open it and the formulas run and everything is updated. Do you know of a way to do this without using this method?

    Thanks for the help!
     
  16. Keebellah

    Keebellah Hans Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,351
    The fact it doesn't recalculate is because of the formula.
    Just a question, and I can solve it.
    Wilol List1 and List2 change in length in due time?

    I can add a vba module that will recalc if a change occurs in either List1 or List2 and atthe same time if you clear a cell somewhere in the list, the cells will move up.

    If this is allright with you I'll do it and send you the modified sheet whet it's ready.

    Can I remove the columns to the right of column E?

    Let me know
     
  17. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/922457