+ Reply to Thread
Results 1 to 19 of 19

Want to Remove Values from an Array

  1. #1
    Registered User
    Join Date
    04-22-2022
    Location
    McDonough, Georgia
    MS-Off Ver
    Office 365
    Posts
    5

    Want to Remove Values from an Array

    I want to reference an existing array and output it again but with 2 values I have listed removed. if one of the listed values occurs more than once, I only want one of the occurrences to be removed. Please see the attached excel file for further clarification.
    Attached Files Attached Files

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

    Re: Want to Remove Values from an Array

    Administrative Note:

    Welcome to the forum.

    Members will tailor the solutions they offer to the version (NOT a release number like 2203) of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.
    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 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,900

    Re: Want to Remove Values from an Array

    Assuming 2205 is Office 365...


    =UNIQUE(F5:F10)

    Do you REALLY want the zeros to be present at all????
    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

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

    Re: Want to Remove Values from an Array

    I don't think that's it, Glenn - one of each of the numbers needs to be removed, so 98 has to come out altogether and one of the instances of 116.

  5. #5
    Registered User
    Join Date
    04-22-2022
    Location
    McDonough, Georgia
    MS-Off Ver
    Office 365
    Posts
    5

    Re: Want to Remove Values from an Array

    Yes Office 365, that formula gets rid of duplicates, but that is not the goal. I do not want the zeros to show, no.

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

    Re: Want to Remove Values from an Array

    Indeed!

    I'm being a bit dense today... best I can do (so far) is helper:

    =MAX(COUNTIF(F$5:F5,TRANSPOSE($D$5:$D$6)))

    and then:

    =IFERROR(INDEX(F:F,AGGREGATE(15,6,ROW($F$5:$F$13)/(($F$5:$F$13<>0)*($G$5:$G$13<>1)),ROWS(I$5:I5))),"")
    Attached Files Attached Files

  7. #7
    Valued Forum Contributor
    Join Date
    07-23-2013
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    988

    Re: Want to Remove Values from an Array

    It feels clunky and long but I have this so far for a solution without a helper column.

    =LET(x,BYROW(IF(ROW(F5:F13)=BYCOL(IF(F5:F13=TRANSPOSE(D5:D6),ROW(F5:F13),""),LAMBDA(col,MIN(col))),0,F5:F13),LAMBDA(row,MIN(row))),FILTER(x,x<>0))

  8. #8
    Registered User
    Join Date
    04-22-2022
    Location
    McDonough, Georgia
    MS-Off Ver
    Office 365
    Posts
    5

    Re: Want to Remove Values from an Array

    it works, thank you!

  9. #9
    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,900

    Re: Want to Remove Values from an Array

    Not sure who you're talking to... but...

    You're welcome.



    It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

    Finally, if that takes care of your original question, please select "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

  10. #10
    Registered User
    Join Date
    04-22-2022
    Location
    McDonough, Georgia
    MS-Off Ver
    Office 365
    Posts
    5

    Re: Want to Remove Values from an Array

    lmao sorry, I'm new to the forum. how do I reply to specific people? Thanks.

  11. #11
    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,406

    Re: Want to Remove Values from an Array

    Just put @ followed by their user name as the start of your message.

  12. #12
    Valued Forum Contributor
    Join Date
    07-23-2013
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    988

    Re: Want to Remove Values from an Array

    You're welcome if it was me you were talking to as well

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

    Re: Want to Remove Values from an Array

    I am fascinated by this question, so have had a play and come up with this:

    =LET(d,D5:D6,f,F5:F13,c,COUNTIF(d,TRANSPOSE(f)),y,DROP(FREQUENCY(d,f),-1),x,FILTER(f,TRANSPOSE(c)&y<>"11"),FILTER(x,x<>0))

    DROP is one of the new text array functions that are currently available on the beta channel, so will most likely be available to all 365 subscribers by the end of 2022. I used it because the FREQUENCY function produces an array one row too long - I am sure in time I'll find a way round this.
    Last edited by AliGW; 04-23-2022 at 03:04 AM.

  14. #14
    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,900

    Re: Want to Remove Values from an Array

    It returns one additional value for any values in the data array (D) which exceed the highest value in the bins array (F)... whether or not such values exist. I cannot envisage any circumstances (in THIS case) where dropping the n+1 th bin can cause a problem... as the last bin (F) can only be populated if a value in the data (D) is > than the highest F... so (by definition) it can never need to be excluded as a duplicate.

  15. #15
    Valued Forum Contributor
    Join Date
    07-23-2013
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    988

    Re: Want to Remove Values from an Array

    Nice solution Ali.

    An "old" (though still fairly new) way instead of DROP might be to use
    INDEX(FREQUENCY(d,f),SEQUENCE(ROWS(f)))
    instead of
    DROP(FREQUENCY(d,f),-1)

    The problem interested me as well, this is my (slightly) less clunky solution that I couldn't get to work yesterday:

    =LET(d,D5:D6,f,F5:F13,x,MATCH(d,TRANSPOSE(f),0)+MIN(ROW(f)-1),FILTER(f,(ISERROR(MATCH(ROW(f),x,0)))*(f<>0)))

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

    Re: Want to Remove Values from an Array

    @Glenn

    Thanks for the explanation - I am going to need to read it again ... SLOWLY!!!

    @Nick


    Another nice one.

  17. #17
    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,900

    Re: Want to Remove Values from an Array

    No shame there, Ali.. I had wo write it slowly... after much number fiddling.

  18. #18
    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,406

    Re: Want to Remove Values from an Array

    There are some really nice new functions in the current beta version that will help to make this sort of thing quite a lot easier, I think. Hopefully they will make it into the release version soon.

    https://techcommunity.microsoft.com/...s/ba-p/3186066

  19. #19
    Valued Forum Contributor
    Join Date
    07-23-2013
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    988

    Re: Want to Remove Values from an Array

    Thanks for the link, a lot of those look very useful and would make some of the solutions on here a lot simpler.

+ 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. how to remove duplicates from an array
    By dmcgov in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-15-2019, 09:54 AM
  2. Replies: 5
    Last Post: 02-22-2018, 01:47 AM
  3. Remove duplicates from array and count number of values
    By JDG94 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-05-2016, 02:02 PM
  4. Filling array with several values and extent array using these values
    By Martijn79 in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 12-30-2015, 07:11 AM
  5. remove duplicate values from array
    By nikko50 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-11-2013, 07:14 PM
  6. remove #VALUE and #N/A from an Array
    By apolloni in forum Excel General
    Replies: 1
    Last Post: 07-01-2010, 01:10 PM
  7. Remove an array
    By gennario in forum Excel General
    Replies: 5
    Last Post: 01-26-2005, 12:06 PM

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