+ Reply to Thread
Results 1 to 24 of 24

Advanced filter with function as criteria range?

  1. #1
    Registered User
    Join Date
    02-14-2008
    Posts
    22

    Advanced filter with function as criteria range?

    Is it possible to do the following:

    I have a worksheet with 6000 rows (W1), and another with 2500 rows (W2).
    I need to check whether the values of W2 are found in the second column of W1. As in if(iserror(search(valuex,worksheet2!B2)),"",A2) ---> resulting in something like:
    If the value is found in the cell B2 of column B on W1, then return its reference which you find in A2, otherwise leave a blank.

    I need to check all 2500 values in all 6000 rows.

    I know for sure that I will have limited hits (max of 200) so I would like to create a list on W2 (the values) where I check if they are found in W1 and return only the 'hits'. I would like to filter out the blanks.

    YOu can filter the blanks, I know, but you need to have a 'full' version (I thought) with all the blanks and the hits and then and only then you can filter.
    But is there a way how I can use the advanced filter, with a criteria range using a function. Something like: criteria range --> if(iserror(search(valuex,worksheet2!B2)),"",A2) is not equal to "".

    Or should I use a list functionality?

  2. #2
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    dakke,

    Please read forum rules and message to cross posters below and then add the link to the cross post

    VBA Noob
    _________________________________________


    Credo Elvem ipsum etian vivere
    _________________________________________
    A message for cross posters

    Please remember to wrap code.

    Forum Rules

    Please add to your signature if you found this link helpful. Excel links !!!

  3. #3
    Registered User
    Join Date
    02-14-2008
    Posts
    22
    I apparently needed to refer to this cross-posts:

    http://www.excelforum.com/showthread.php?t=634938
    http://www.excelforum.com/showthread...55#post1887255

  4. #4
    Registered User
    Join Date
    02-14-2008
    Posts
    22
    I did not find a satisfactory solution and tried the following:

    Lists:
    no good, since I can not drag the lists over the 6000 rows, creating 6000 list would be a pain.

    Advanced filter:
    not able to insert a function and again need to create up to 6000 advanced filters: auch

    Function:
    No solution found yet, I can list the non blanks if I have the entire list (blanks and non blanks) with a function that tests for each 6000 row by individually checking each 2600 values. Leads to excel files up to 100 MB, which is not cool.

    VBA:
    not familiar with it, so I can not trust a VBA function since I feel like losing control over the whole process.

  5. #5
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229
    I don't understand the description that you gave, can you post a sheet with an example of what you want.
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  6. #6
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    dakke,

    The cross post to the other excel forum where you got an answer.

    VBA Noob

  7. #7
    Registered User
    Join Date
    02-14-2008
    Posts
    22
    No problem, look at the attach for a small sample...

    I had help on creating a VBA functionality, but I'm really not familiar with the VBA stuff. You can find the VBA out here:

    http://www.ozgrid.com/forum/showthread.php?t=85736

    Here's the file:
    example.xls.zip

    Thanks.

  8. #8
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229
    Are there always 3 columns in the Range! sheet or might there be more?
    Is the list in Range! dynamic, does it change size?

  9. #9
    Registered User
    Join Date
    02-14-2008
    Posts
    22
    The range has, to be correct, 4 columns, 6 if you include the unique number the reference that needs to be returned.

    So there are 4 columns that need to be checked (C to F), A is the unique 'key' (1 to 6000) and B being the returned value (in case of a 'hit').

    The values never change, they remain as the are.

    The range does indeed change, sometimes only 600, sometimes indeed 6000.

  10. #10
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229
    That does not match the example posted. The values returned on the "desired" sheet seem to have come from column A of "range", not from column B.

    There is also an inconsistency in the example; On "desired" sheet, "value 1" returns "range 1" twice, but "value 2" returns "range 2" only once.
    Some instances of "value1" on the range! sheet had no space. The result sheet implied that this was a typo.

    I also don't see how on desired!, value 5 returns range 3
    Last edited by mikerickson; 02-27-2008 at 09:09 PM.

  11. #11
    Registered User
    Join Date
    02-14-2008
    Posts
    22
    Sorry about the confusion, my apologies.

    It was clearly not the correct file. I edited this one, trying to get the result but I failed in doing so, yet did not correct it back to the original one.

    Anyway, this should be correct.

    example.xls.zip

    Unique key for both values and lookups.
    I indicated the range and the ref for the lookups.

  12. #12
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229
    I think this UDF will work for you

    MatchArray(testString, dataRange, [indexNum])

    If the optional third argument is omitted, MatchArray is an array function.
    =MatchArray("value 1")
    returns an array of those entries in the first column of the data range where "value 1" is a sub-string of either of the cells in the second or third column.

    Example,
    =MatchArray("value 1",range!$B:$D)
    returns the array {"result 1", "result 2", "result 4", "result 5"}

    The third optional indexNum value does not need multi-key entry, it returns the indexNum'th element of the array MatchArray(testString, dataArray)
    eg.

    =MatchArray("value 1",range!$B:$D, 1) returns "result 1"
    =MatchArray("value 1",range!$B:$D, 2) returns "result 2"
    =MatchArray("value 1",range!$B:$D, 3) returns "result 4"
    =MatchArray("value 1",range!$B:$D, 4) returns "result 5"

    If the index is higher than the number of matches, the empty string is returned.

    Here's an example file:
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    02-14-2008
    Posts
    22
    Much appreciated. It looks indeed what I needed.

    I'll test it asap, adjust it to the actual data and get back to you as soon as it works.

  14. #14
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229
    Errata to attachment:
    The instruction in NewSheet!I6 should be

    2) type =MatchArray("value 1", result!$B:$D) in the Formula bar

  15. #15
    Registered User
    Join Date
    02-14-2008
    Posts
    22
    The function keeps on returning #Name?

    I copied the exact code, tried all three versions, but the error does not change.

    I must overlook something very basic but...

  16. #16
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229
    The range argument (the second one) should have the column of cellswith "result 1","result 2", etc. as the left column.

    The columns being tested to see if they hold the testString are the second and third columns of the dataRange.

    Are you entering it as an array formula, with the multi-key press?

    Have you tried entering it with the Insert Formula dialog? That helps with some UDFs.

    Edit: I just remembered, the file you posted had Calculation set to Manual. Have you tried Automatic?

    The code should go in a normal module, not a sheet's code module or ThisWorkbook.
    Last edited by mikerickson; 02-28-2008 at 12:59 AM.

  17. #17
    Registered User
    Join Date
    02-14-2008
    Posts
    22
    Could it be that this is a mac vs win thing?

    I found out that if I open the file, I am prompted to enable the macro's in the file. When I disable them, I am told that some can not be disabled. Enabling them does work.

    When I open a new document I can not find the function MatchArray, it simply does not exist on my mac.
    When I however enable your doc and I add a function to a cell range as you instructed, go to Insert > Function I indeed find the MatchArray function.

    So it is not there in a standard xls file, but yours does contains the function. Any thoughts or doesn't this make sense at all?

  18. #18
    Registered User
    Join Date
    02-14-2008
    Posts
    22
    I must have something to do with the fact that a standaard excel file does not have that functionality.

    I adjusted your file and now it works just fine. I should delve in your solution since this is such a powerful way of dealing with the issue.

    Thank you very very much!

  19. #19
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229
    You need to enable the macros for the UDF to work.
    I wrote it on a Mac, so it should work on yours.

    Yes, it makes perfect sense. The workbook has a normal code module containing the VB code for the UDF.
    The code module is part of the workbook in the same way that a sheet is part of a workbook.
    If the workbook is SavedAs, the code module is saved along with it and a copy goes with the copy of the workbook.

  20. #20
    Registered User
    Join Date
    02-14-2008
    Posts
    22
    A final question (two actually):
    - If a value is searched twice (in row 400 I have the same value as in row 800), is it possible that row 800 returns 0 results? Just checking if the thing is actually doing what I think it is.

    Could you point me to the module? It would be interesting to take a look at.

  21. #21
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229
    I don't understand the question about row 800.
    Opening the VBEditor will show you the code modules for your Project.

  22. #22
    Registered User
    Join Date
    02-14-2008
    Posts
    22
    Thanks again for the reply.

    I thought match was case in-sensitive. But it appears that it codes for Value 1, and not for value 1.
    Is there a quick fix to resolve that? I need a case-insensitive search function.

  23. #23
    Registered User
    Join Date
    04-14-2021
    Location
    Long Island, NY
    MS-Off Ver
    Office 365
    Posts
    37

    Re: Advanced filter with function as criteria range?

    Quote Originally Posted by mikerickson View Post
    You need to enable the macros for the UDF to work.
    I wrote it on a Mac, so it should work on yours.

    Yes, it makes perfect sense. The workbook has a normal code module containing the VB code for the UDF.
    The code module is part of the workbook in the same way that a sheet is part of a workbook.
    If the workbook is SavedAs, the code module is saved along with it and a copy goes with the copy of the workbook.


    Hi Mike,

    If you have time, would you mind looking at my code?

    https://www.excelforum.com/excel-pro...ml#post5528073

  24. #24
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,571

    Re: Advanced filter with function as criteria range?

    knykil
    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
    Ben Van Johnson

+ 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