Excel 2007 : Array formula to extract unique values from a range

**Motox** · 11-24-2011, 04:55 AM

Hi forum,

I am using Excel 2010.

I have a range named Data that runs down one column. There is a lot of duplication of the values within the range

I am trying to use the following formula (which I got out of an Excel book) to get a list of unique values:

=INDEX(Data,SMALL(IF(MATCH(Data,Data,0)=ROW(INDIRECT("1:"&ROWS(Data))),MATCH(Data,Data,0),""),ROW(INDIRECT("1:"&ROWS(Data)))))

I enter the formula into a single cell then press Ctrl+Shft+Enter

All I get is the value that is on top of the list.

What am I doing wrong and how can I get a list of ALL unique values from the range?

Thanks very much

Dave

**Motox** · 11-24-2011, 05:25 AM

Hi, I found what I was doing wrong- I had to select all the cells beside the range (not a single cell) then type the formula.

Is there a way to copy this formula and paste it in so I dont have to type it each time I use it?
If I copy and paste or use Ctrl+V it doesnt work.

Thanks very much

Dave

**Azam Ali** · 11-24-2011, 05:39 AM

upload a sample workbook with sample data and your desired results

In order to attach a file Click the Go Advanced button, and click on the Manage Attachments button

**Fotis1991** · 11-24-2011, 06:11 AM

Ηι

I think there is most easy way to get the effect you.
Assuming that the values, are in column A, starting in A2,then:
1) Name all your range as "List"
2) In B2, put the following formula and drag as needed.

=IF(COUNTIF(List;A2)>1;"";"unique")