+ Reply to Thread
Results 1 to 17 of 17

Using Filter function with array criterias

  1. #1
    Registered User
    Join Date
    03-03-2019
    Location
    Hong Kong
    MS-Off Ver
    Office 365
    Posts
    17

    Using Filter function with array criterias

    Hi guys!

    I have a feeling this one is difficult if not impossible to solve (probably am not savvy enough with excel for this). Essentially I have two columns, one with names and one with values. Each name has multiple values. Next is I am generating a random array of names. Once the names are generated, I want to return minimum value of the names generated instead of the names themselves. And if there are duplicate names (which there are), would it be possible to return the minimum value for the first one and second most minimum for the second one? I reckon will be needing to create another array and then overlaying them, but I dunno how to even begin writing that.

    Note input and output are both supposed to be in dynamic array form. I will generate a list of random names, and from that I would like the minimum values of the generated names (and second most minimum for the duplicates etc)

    Thanks all
    Attached Files Attached Files
    Last edited by kohno71; 05-23-2022 at 12:48 PM.

  2. #2
    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,401

    Re: Using Filter function with array criterias

    If you still have Excel 2016, then you can't use dynamic array formulae. If you don't, then your profile is not up-to-date and needs amending, please.

    Which is it?
    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.

  3. #3
    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,401

    Re: Using Filter function with array criterias

    If you DO have a newer version, as your workbook suggests, then you can use this:

    =LET(n,INDEX($B$2:$B$68,RANDARRAY(10,,0,66,1)),m,MINIFS($C$2:$C$68,$B$2:$B$68,n),CHOOSE({1,2},n,m))
    Attached Images Attached Images
    Attached Files Attached Files

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,899

    Re: Using Filter function with array criterias

    You've hit the same problem as me, Ali. Your generates ONLY the minimum value, not the 2nd. 3rd smallest for duplicates....
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  5. #5
    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,401

    Re: Using Filter function with array criterias

    I see you have marked this as solved and updated your profile - thank you.

    It would have been nice if you had thanked me for helping you, but it's not too late!

    You may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

  6. #6
    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,401

    Re: Using Filter function with array criterias

    Quote Originally Posted by Glenn Kennedy View Post
    You've hit the same problem as me, Ali. Your generates ONLY the minimum value, not the 2nd. 3rd smallest for duplicates....
    I didn't realise that was required - and the OP has marked this as solved! Mmm ...

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,899

    Re: Using Filter function with array criterias

    This was as far as I had got.... I failed miserably trying to get it all to work in one dynamic array:

    =LET(d,$B$2:$B$68,nd,COUNTA(d),r,10,n,INDEX(d,RANDARRAY(r,,0,nd,1)),z,MMULT((SEQUENCE(r)>=SEQUENCE(,r))*(n=TRANSPOSE(n)),SEQUENCE(r)^0),CHOOSE({1,2},n,z))

    and:

    =AGGREGATE(15,6,$C$2:$C$68/($B$2:$B$68=K2),L2)
    copied down.
    Attached Files Attached Files

  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,401

    Re: Using Filter function with array criterias

    There was something similar to this recently - I've been hunting through my notes, but can't find the relevant solution. I'll keep looking.

  9. #9
    Registered User
    Join Date
    03-03-2019
    Location
    Hong Kong
    MS-Off Ver
    Office 365
    Posts
    17

    Re: Using Filter function with array criterias

    Hi Guys! Yeah I marked as solved because I assumed this was far as we all could go. Indeed my goal is to get 2nd and 3rd min as well. But if it cant be done, AliGW's solution will have to suffice. Thanks a lot for all your help guys, will add reputation as advised by AliGW!!

  10. #10
    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,401

    Re: Using Filter function with array criterias

    I suggest you leave the thread as unsolved - someone will have a solution for you. Don't make assumptions!

    I will remove the solved tag for you.
    Last edited by AliGW; 05-23-2022 at 08:01 AM. Reason: Typo fixed.

  11. #11
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    China Shanghai
    MS-Off Ver
    365 V2402 and WPS V2022
    Posts
    3,327

    Re: Using Filter function with array criterias

    Cell G2 formula

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Cell H2 fomula , Drag down
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    office 365
    Cell H2 fomula , Drag down
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by wk9128; 05-23-2022 at 09:17 AM.

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

    Re: Using Filter function with array criterias

    How about
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  13. #13
    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,401

    Re: Using Filter function with array criterias

    That's a good one, Fluff, although I think BYROW is only available to beta insiders at the moment.

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

    Re: Using Filter function with array criterias

    Thanks for that, don't like having to do the concatenation, but it seems to work.
    The Lambda functions are on general release, but may not be available to people on the semi-annual channel yet.
    I'm just on the normal Monthly channel, rather than the insiders.

  15. #15
    Registered User
    Join Date
    03-03-2019
    Location
    Hong Kong
    MS-Off Ver
    Office 365
    Posts
    17

    Re: Using Filter function with array criterias

    Awesome, that works fluff! Exactly what I was looking for. Thank you so much Fluff and everyone!

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

    Re: Using Filter function with array criterias

    Glad to help & thanks for the feedback.
    Here's a slightly shorter version that doesn't use the concatenation.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  17. #17
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Using Filter function with array criterias

    Please try

    =LET(a,B2:C68,b,SORT(a,{1,2}),s,ROWS(a),r,RANDARRAY(s),FILTER(SORTBY(b,SORTBY(r,INDEX(b,,1),1,r,1)),SEQUENCE(s)<11))
    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. [SOLVED] Excel Filter function matching to a Spill Array
    By ibuhary in forum Excel General
    Replies: 16
    Last Post: 10-25-2020, 11:05 PM
  2. [SOLVED] Is possible pass array to function Filter mach parameter?
    By marreco in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 04-29-2020, 01:19 PM
  3. 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
  4. VBA Function to filter data from an array
    By car___ in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-19-2014, 04:31 PM
  5. [SOLVED] Filter data using Array Function
    By HaroonSid in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 04-23-2014, 08:45 AM
  6. VBA Filter criterias
    By NOVO_N in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-28-2014, 06:02 AM
  7. VBA Filter Array by Two date Criterias, Help!
    By Crebsington in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-22-2011, 07:03 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