Array Formula to Remove Duplicates from List

[cscribner]

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!

 

[jeffreybrown]

Hi & Welcome to the Board,

Take a look at this thread especially post #12.

http://www.mrexcel.com/forum/showthread.php?t=460154&page=2

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

http://www.contextures.com/xladvfilter01.html

 

[taigovinda]

Welcome to the board!

I agree, advanced filter is a great way to go. You might also have a look at this thread http://www.mrexcel.com/forum/showthread.php?t=435669 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.

 

[cscribner]

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!

 

[justanna111]

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)),"")}

 

[msherwin]

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

 

[TomHouy]

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.

 

[DocAElstein]

........

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!

 

[wilnexpc]

This works great,

the range you want to pull unique value / text from is the A2:A17 column; the range you want your unique value/text to be in starts at cell C2 (in the example below.

........

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!

 

[yosegmail]

{=COUNTIF(A8:A43,A8:A43)}

this formula counts then number of dublicates