+ Reply to Thread
Results 1 to 17 of 17

Excel Filter function matching to a Spill Array

  1. #1
    Forum Contributor
    Join Date
    06-01-2014
    Location
    Riyadh, Saudi Arabia
    MS-Off Ver
    Microsoft 365
    Posts
    224

    Question Excel Filter function matching to a Spill Array

    Hi All,

    Is there any way to make the New Office 365 Filter Function to work by rows matching to an existing Spill Array ?

    What I am trying to do is
    e.g.

    =FILTER(Source_range,Lookup_Colum=A2#,"NotAvailable")

    Any help is appreciated
    Last edited by ibuhary; 10-24-2020 at 01:19 PM.

  2. #2
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: Excel Filter function matching to a Spill Array

    If you mean you want all rows in Source_range for which the corresponding value in Lookup_Column has a match in A2#, try

    =FILTER(Source_range,COUNTIF(A2#,Lookup_Column),"NotAvailable")

  3. #3
    Forum Contributor
    Join Date
    06-01-2014
    Location
    Riyadh, Saudi Arabia
    MS-Off Ver
    Microsoft 365
    Posts
    224

    Re: Excel Filter function matching to a Spill Array

    I tried your solution and doesn't seem to give the correct output.

    Here is a public link for the file with some test data:https://1drv.ms/x/s!AOxmQ6JIaV-cgjs

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Excel Filter function matching to a Spill Array

    Attach the workbook here, please. Instructions are at the top of the page.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  5. #5
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Excel Filter function matching to a Spill Array

    In what way isn't the formula working?
    It looks to be working correctly, as it shows the rows for the 1st 2 values in col C & the last 2 don't exist.

  6. #6
    Forum Contributor
    Join Date
    06-01-2014
    Location
    Riyadh, Saudi Arabia
    MS-Off Ver
    Microsoft 365
    Posts
    224

    Re: Excel Filter function matching to a Spill Array

    Yes, correct. I was looking at the order of Items in Column C, rather its taking the order of the LookUp Sheet.
    Sorry for the confusion.
    Thank you for pointing it out.

  7. #7
    Forum Contributor
    Join Date
    06-01-2014
    Location
    Riyadh, Saudi Arabia
    MS-Off Ver
    Microsoft 365
    Posts
    224

    Re: Excel Filter function matching to a Spill Array

    I am unable to mark this thread as Solved !

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Excel Filter function matching to a Spill Array

    Edit your opening post and choose solved from the drop-down selector.

  9. #9
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Excel Filter function matching to a Spill Array

    If you want to sort the data based on col C, try
    =LET(Fltr,FILTER(STOCK_EAN!$A2:$G5334,COUNTIF(C4#,STOCK_EAN!$G2:$G5334),"Not Available"),SORTBY(Fltr,MATCH(INDEX(Fltr,,7),C4#,0)))

  10. #10
    Forum Contributor
    Join Date
    06-01-2014
    Location
    Riyadh, Saudi Arabia
    MS-Off Ver
    Microsoft 365
    Posts
    224

    Re: Excel Filter function matching to a Spill Array

    We could do it before using thread tools, that was an easy option !

  11. #11
    Forum Contributor
    Join Date
    06-01-2014
    Location
    Riyadh, Saudi Arabia
    MS-Off Ver
    Microsoft 365
    Posts
    224

    Re: Excel Filter function matching to a Spill Array

    Thanks @Fluff13
    That was awesome ! Thank you again for going the extra mile : )
    Appreciate that.

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Excel Filter function matching to a Spill Array

    Quote Originally Posted by ibuhary View Post
    We could do it before using thread tools, that was an easy option !
    We know. That option disappeared inexplicably about three days ago. We are trying to get it fixed.

    We also have a really good attachment facility that is still working, but you refuse to use, despite my having pointed it out to you more than once before.

  13. #13
    Forum Contributor
    Join Date
    06-01-2014
    Location
    Riyadh, Saudi Arabia
    MS-Off Ver
    Microsoft 365
    Posts
    224

    Re: Excel Filter function matching to a Spill Array

    Hi @AliGW,

    Thank you for your reply.
    My Apologies about the Attachment, unfortunately I don't have access to a file in any Local Drive to attach.
    That's the reason I used the file in the cloud. Since there is a huge shift globally towards online drives, isn't it a good idea to allow users an alternate option ? Also it will reduce duplicate storage.
    BR,

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Excel Filter function matching to a Spill Array

    No. The point about this forum is that it is a database of help. It’s not just about solving your issue today: it’s about having the relevant materials here in the future fir anyone else who finds this thread useful. Your sample file won’t be there if you’ve moved it, but it would still be here. Next time, please do as I have asked. Thank you.

  15. #15
    Forum Contributor
    Join Date
    06-01-2014
    Location
    Riyadh, Saudi Arabia
    MS-Off Ver
    Microsoft 365
    Posts
    224

    Re: Excel Filter function matching to a Spill Array

    Okay, I understand.
    But at the moment I don't have access to a Local Drive to manage attachments.
    Let me try to do it as soon as I have access to one.
    Thank you,
    BR.

  16. #16
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Excel Filter function matching to a Spill Array

    It's not hard to download the file in question to your PC and upload it from there ...

  17. #17
    Forum Contributor
    Join Date
    06-01-2014
    Location
    Riyadh, Saudi Arabia
    MS-Off Ver
    Microsoft 365
    Posts
    224

    Re: Excel Filter function matching to a Spill Array

    Attachment Done.
    Attached Files Attached Files

+ 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: 5
    Last Post: 01-27-2021, 12:07 PM
  2. [SOLVED] Filter Causing SPILL# in IF Formula
    By Fugdkn in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-18-2020, 06:29 AM
  3. Replies: 3
    Last Post: 08-09-2020, 10:57 AM
  4. [SOLVED] Using the =IF function is ok on single cell but gives#SPILL on a range
    By Brian Mc in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-12-2020, 05:37 PM
  5. Google Sheets (FILTER + ARRAY) Function translated to my EXCEL Sheet
    By dmcmaste in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-31-2020, 01:35 PM
  6. [SOLVED] Function/Array for matching data from tree column
    By Karnik in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-30-2019, 12:32 AM

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