Results 1 to 8 of 8

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

    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

  7. #7
    New Member
    Join Date
    Mar 2014
    Posts
    8

    Default Re: Array Formula to Remove Duplicates from List

    Another option, if you don't mind having an extra sheet in your workbook, is to simply create a pivot table with that data set, then drag that column of data that you want condensed into a "row" on the pivot table layout. It will automatically condense it down to unique values. From there, you can just pull this list into another sheet with a simple "=" formula.

  8. #8
    Board Regular DocAElstein's Avatar
    Join Date
    May 2014
    Location
    Gone
    Posts
    1,039

    Default Re: Array Formula to Remove Duplicates from List

    ........

    I used this formula

    {=IFERROR(INDEX($A$2:$A$17, MATCH(0, COUNTIF(C$1:$C1, $A$2:$A$17), 0),1),"")}

    here:

    http://www.mrexcel.com/forum/excel-questions/818208-code-move-vertical-horizontal-4.html?

    . _ I can't remember where I got it from, but I did 'ava a go at explaining it... clourfully!

    Regards
    Mustava Klatsche!
    Google first with: site:MrExcel.com "Short Title or Theme of wot you want"
    Use Code Tags: Highlight code; click on the # sign at the top of the thread window, or Consider using The VB Code HTML Maker and Spreadsheet HTML Maker: http://www.mrexcel.com/vbaddin.shtml ; MrExcel HTML Maker
    ; Click here to download the MrExcel HTML Maker.
    -See bottom of the page (Win & Mac): http://excelmatters.com/excel-forums/
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    XL 2003 2007 2010 Not mac

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