+ Reply to Thread
Results 1 to 6 of 6

UNIQUE FILTER Formula to Mimic "Remove Duplicates"

  1. #1
    Registered User
    Join Date
    11-23-2022
    Location
    Texas, USA
    MS-Off Ver
    365
    Posts
    5

    Lightbulb UNIQUE FILTER Formula to Mimic "Remove Duplicates"

    Hi, with office365 released new formula functions such as "FILTER" and "UNIQUE", I'd like to combine the two formulas to create something that would mimic the "Remove Duplicates" function.

    See Attach data set,
    if I were to use the "Remove Duplicates" Function based on "employee number" it would keep the first row and remove duplicate rows afterward.

    Can UNIQUE/FILTER formula do the samething?
    Attached Files Attached Files

  2. #2
    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: UNIQUE FILTER Formula to Mimic "Remove Duplicates"

    If your O365 version supports MAP, try:

    =FILTER(A2:F4,MAP(B2:B4,LAMBDA(x,COUNTIF(B2:x,x)))=1)
    Attached Files Attached Files
    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

  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: UNIQUE FILTER Formula to Mimic "Remove Duplicates"

    If it doesn't have map... pleas tell us what VERSION of O365 you do have.
    Attached Images Attached Images

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

    Re: UNIQUE FILTER Formula to Mimic "Remove Duplicates"

    and if your O365 is a bit older, try:

    =FILTER(A2:F4,MMULT((SEQUENCE(ROWS(B2:B4))>=SEQUENCE(,ROWS(B2:B4)))*(B2:B4=TRANSPOSE(B2:B4)),ROW(B2:B4)^0)=1)
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    11-23-2022
    Location
    Texas, USA
    MS-Off Ver
    365
    Posts
    5

    Re: UNIQUE FILTER Formula to Mimic "Remove Duplicates"

    The MAP one works!!! Thank you, sir!

    It blows my mind how experts such as yourself understand all these formulas... I did not even know these functions existed........ SEQUENCE..MAP...LAMBDA..transpose!??
    Did you self-learn all these? What would be the best way to learn these non-standard excel formulas... appreciate you.

  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: UNIQUE FILTER Formula to Mimic "Remove Duplicates"

    I just picked them up here, watching and learning from others...

    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 click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

+ 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] Need Formula mimic "Olympic Medal Award" process
    By skydivetom in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-07-2020, 07:05 AM
  2. Replies: 13
    Last Post: 10-22-2019, 02:14 AM
  3. Replies: 7
    Last Post: 10-21-2019, 03:34 PM
  4. [SOLVED] how to remove duplicates by "person", not by entire column
    By eeps24 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-08-2017, 12:39 PM
  5. Replies: 1
    Last Post: 01-22-2016, 09:21 AM
  6. [SOLVED] "Unique" filter allowing duplicates
    By Ochimus in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-08-2015, 01:41 PM
  7. [SOLVED] "Live" remove duplicates formula
    By TPDave in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-25-2015, 06:30 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