How to extract unique distinct values of a column using a formula 1

[johnbezzina]

How to extract unique distinct values of a column using a formula (NOT a filter)

Is there an easy way to do this? I have been looking high and low and I simply cannot find an easy to use formula.

My needs are quite simple.. I have a list of around 500 items and a good number of these are duplicates, all I need is to extract a list of individual items into another column.

thanks!!

 

[SkipVought]

Hi,

What version Excel?

all I need is to extract a list of individual items into another column.

You could use the Advanced Filter.

If 2007, could use Unique Values.

Could use MS Query.

Could use the Pivottable Wizard.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[byundt]

The quickest and easiest ways of getting the unique items are either to use Advanced Filter or to use a VBA user-defined function.

It is possible to do it with an array formula, however. This has traditionally been a two step process, such as described at Chip Pearson's web site (

http://www.cpearson.com/excel/duplicat.htm#ExtractingUnique

), with one formula being used to extract the unique items and another to eliminate the blanks. As a result, an auxiliary column is needed for the extraction.

The following formula does the job all in one step (assuming the list contains no blanks). It returns error values once the unique values are exhausted. And it works for both numeric and text data:

=INDEX(List,SMALL(IF(MATCH(List,List,0)=ROW(List)-mmm,ROW(List)-mmm,65536),ROW()-nnn)) Array formula, so CTRL + Shift + Enter
mmm is one less than the first row in List
nnnn is one less than the first row receiving the formula

Here is a longer array formula that will tolerate blanks in the raw list and a list range that extends beyond your data. This formula may be copied down, and will return blank strings when the unique values have been exhausted. Note that the range specified by List must extend beyond your raw data.
="" & INDEX(List, SMALL(IF((List<>"")*(MATCH(IF(List="","",List),IF(List="","",List),0)=ROW(List)-mmm),ROW(List)-mmm,last-mmm),ROW()-nnn))
last is the row number of a blank element in List. It is most convenient if List is a fixed range extending beyond any expected data--so last would be the row number of the blank at the end of that range.

Note: this last formula will convert any numbers into text.

Brad

 

[arthurbr]

=INDEX($A$2:$A$11, MATCH(0, IF(ISBLANK($A$2:$A$11), 1, COUNTIF(B1:$B$1, $A$2:$A$11)), 0)) Enter this formula in B2 + CTRL + SHIFT + ENTER copied down as far as necessary

Supposing your range is A2:A11

 

[johnbezzina]

Thanks Guys. I opted to try the formula sent by arthurbr ince it looks simple enough but I am getting a list of zeros. BTW... my list is made up of text entries not numbers

 

[SkipVought]

NOTHING returned from a formula is ZERO unless you substitute a "" zero length string using an IF function.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[GlennUK]

Thanks Guys. I opted to try the formula sent by arthurbr ince it looks simple enough but I am getting a list of zeros. BTW... my list is made up of text entries not numbers

There's nothing wrong with the formula. Did you type it in, or copy it from here?

Did you adjust it some way? If so, show your formula.

Did you remember to enter it using Ctrl-Shift-Enter?

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[johnbezzina]

I just noticed that you get a zero when you place the formula in column B (as was suggested) if you place the formula someplace else and change the range after the COUNTIF so that it will not be in the same column as the formula you get a result but only the first entry. I have included an example.

THANKS!!

 

[byundt]

Please paste the following formula in cell B2 of your sample workbook. Array-enter it (Control + Shift + Enter). Then copy it down. You should see your unique items followed by #N/A once the list is exhausted.
=INDEX($A$1:$A$24, MATCH(0, IF(ISBLANK($A$1:$A$24), 1, COUNTIF(B$1:$B1, $A$1:$A$24)), 0))

I believe your problem was in modifying the suggested formula but failing to understand its tricky nature.

 

[johnbezzina]

THANKS!!!! it worked just fine now.

 

[BlueHorizon]

Hi,

Great answers! BTW, Excel 2007 has a new "Remove Duplicates" function (next to Text to Columns) on the Data Tab. Makes this job really easy...

Happy Holidays!

Best,
Blue Horizon

 

[johnbezzina]

Yes, I know about that Blue Horizons... works fine if you have a handfull of columns to work with but I have over a 100!!

Thanks Guys!

 

[byundt]

If you have Excel 2007 or later, I'd suggest implementing the formula like this. It will now return an empty string when the unique elements in the list are exhausted.
=IFERROR(INDEX($A$1:$A$240,MATCH(0,IF($A$1:$A$240="",1,COUNTIF(B1:$B$1,$A$1:$A$240)),0)),"")

The reference to row 240 is arbitrary, and may extend beyond the end of your data.