+ Reply to Thread
Results 1 to 7 of 7

Array formula to filter a results set only matches first value in the array

  1. #1
    Registered User
    Join Date
    05-22-2012
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    5

    Array formula to filter a results set only matches first value in the array

    Hi there,

    I have about 70000 rows of data that I would like to filter based on a list of about 50 text strings held in a second sheet. I would like all occurrences of the 50 text strings to be filtered from the 70000 rows. I am using a "substring" search with an array formula and a true/false filter to show the matches.

    The attached file Customer List Filtering example guru.xlsx is a stripped down example of what I am trying to achieve taken from this excellent thread on filtering large amounts of data. This uses a dynamic named range BadCust as a basis for the array of values on the Bad Customer sheet. The array formula used is {=ISNUMBER(SEARCH(BadCust, B2))} but this only seems to work against the 1st value ABC in the Bad Customers worksheet. EFG which is also in the BadCust list shows FALSE. I would expect both of these to be TRUE.

    I've tried replacing the dynamic named range with a fixed range of cells but that still only produces a match with the 1st value in the second sheet.

    Any ideas?

    Thanks, Paul

  2. #2
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Array formula to filter a results set only matches first value in the array

    Paul,

    Welcome to the forum!
    Attached is a modified version of your example workbook.

    I changed the named range formula to be:
    Please Login or Register  to view this content.
    I made that change so that it wouldn't include a blank cell in the BadCust list.
    Then, in the 'Test Data' worksheet, in cell A2 and copied down is this non-array formula:
    Please Login or Register  to view this content.
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    05-22-2012
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Array formula to filter a results set only matches first value in the array

    Thank you for the welcome. There is some great stuff on here :-)

    Your solution works tigeravatar - much appreciated. It solves my immediate problem thank you. Was the problem with the original solution the array formula?

    Now I just need to find a way to make your suggestion run as fast as an array formula! It takes quite some time on my core 2 duo machine to process 70,000 records against the list of 50 or so filters!

    Paul

  4. #4
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Array formula to filter a results set only matches first value in the array

    Paul,

    No, the problem wasn't the array formula. Sumproduct calculates like an array formula. Give this version a try in 'Test Data'!A2 and copy down. Note that this is an array formula and as such must be entered with Ctrl+Shift+Enter:
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    05-22-2012
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Array formula to filter a results set only matches first value in the array

    Tried that and I could not get it to return results. Shouldn't the first two parameters be transposed? i.e.

    Please Login or Register  to view this content.
    In the real example I am working with I am searching for a substring from BadCust in B2. I'm not aware that MATCH can do that and perhaps that is why your most recent suggestion is not returning results?

    Paul

  6. #6
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Array formula to filter a results set only matches first value in the array

    ulthima,

    Normally, that is correct. When you do it backwards, the Match is capable of returning an array of results, hence the need to array enter (Ctrl+Shift+Enter) the formula.

    Attached is a version of the workbook that contains the proposed formula and it is working as expected.

  7. #7
    Registered User
    Join Date
    05-22-2012
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Array formula to filter a results set only matches first value in the array

    Thanks and kudos to your greater excel knowledge Tigeravatar. Much appreciated.
    Paul

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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