# 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. ## 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. ## 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...

3. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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!

#### Posting Permissions

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