Results 1 to 6 of 6

Array Formula to Remove Duplicates from List

This is a discussion on Array Formula to Remove Duplicates from List within the Excel Questions forums, part of the Question Forums category; I'm looking for help with an array formula that will condense a long list like this: a a a b ...

  1. #1
    New Member
    Join Date
    Apr 2010
    Posts
    3

    Default Array Formula to Remove Duplicates from List

    I'm looking for help with an array formula that will condense a long list like this:

    a
    a
    a
    b
    c
    b
    d

    To this

    a
    b
    c
    d

    thanks!

  2. #2
    Board Regular jeffreybrown's Avatar
    Join Date
    Jul 2004
    Location
    San Antonio, Texas
    Posts
    4,383

    Default Re: Array Formula to Remove Duplicates from List

    Hi & Welcome to the Board,

    Take a look at this thread especially post #12.

    Formula to extract unique values to an array

    BTW: The advanced filter IMO is still the best way to go...

    http://www.contextures.com/xladvfilter01.html
    Last edited by jeffreybrown; Apr 10th, 2010 at 10:00 AM.
    Jeff

  3. #3
    Board Regular
    Join Date
    Mar 2007
    Location
    Chicago Area
    Posts
    2,607

    Default Re: Array Formula to Remove Duplicates from List

    Welcome to the board!

    I agree, advanced filter is a great way to go. You might also have a look at this thread data comparison which has code to take in any number of lists from one or more columns, sheets or books, and spit out a single list of unique values. I have the macro from post #5 in my personal book and find it handy.

  4. #4
    New Member
    Join Date
    Apr 2010
    Posts
    3

    Default Re: Array Formula to Remove Duplicates from List

    You guys are awesome! Thanks so much--it works just the way I hoped. I'm still trying to get my head around the match function portion, which is the meat of the argument, but at least I can move forward in the meantime!

  5. #5
    New Member
    Join Date
    Nov 2013
    Posts
    2

    Default Re: Array Formula to Remove Duplicates from List

    Hi everyone

    I am trying to do a similar thing Except my issue is, the function only works for numerical format or letter is fine. But if it is a text number format like "001", it wont work. Can anyone help here?
    My function is this($D$11 is referencing the range of data I am filtering;F$18:F18 is the first data I found in that range):

    {=IFERROR(INDEX(INDIRECT($D$11),MATCH(0,COUNTIF(INDIRECT($D$11),"<"&INDIRECT($D$11))+COUNT(INDIRECT($D$11))*ISTEXT(INDIRECT($D$11))+100000*ISBLANK(INDIRECT($D$11))-SUM(COUNTIF(INDIRECT($D$11),"="&F$18:F18)),0)),"")}

  6. #6
    New Member
    Join Date
    Jul 2014
    Posts
    4

    Default Re: Array Formula to Remove Duplicates from List

    Maybe I'm a little late to the party, but I ran across a really nice solution that uses a couple formulas to create a column that contains only the unique values from another column. It does require the use of a helper column, but you can always hide that. So far, I have tested this solution and it continues to update the values in my "duplicates" list.

    Remove Duplicates or Create a List of Unique Records using Excel Formula

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com