Returning a Unique Result Set from a List

[Mark T]

Is there a way in Excel to have a function return a list
of unqiue values from a column of data? Simple example...

COLUMN A
Fred
Mary
Fred
Kevin
Kevin
Mary
Sam
Mary

I want the unique values in Column A placed in another
area of the sheet. The Desired result would look like:
Fred
Mary
Kevin
Sam

Thanks for any help!

 

[Debra Dalgleish]

You can use an Advanced Filter to extract the unique values. There are
some instructions here:

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

 

[Guest]

That works but I need it to happen inside a function so
the list can be kept current as the data changes. I do
not want to perform this filter manually each time the
data changes. Any other ideas on how to get it work
inside a function?

 

[Debra Dalgleish]

You can add a column to the left of the column of duplicate data. With
your data starting in cell B2, enter the following formula in cell A2:
=IF(COUNTIF(B$1:B2,B2)=1,MAX(A$1:A1)+1,"")

Copy this down to the last row of data.

In a column to the right, enter the following formula in row 2:
=IF(MAX(NameCount)<ROW(1:1),"",VLOOKUP(ROW(1:1),NameList,2))

NameCount is a dynamic range for the items in column A.
NameList is a dynamic range for the items in column A:B.

Copy the formula down as far required.

There's a sample workbook here:

http://www.contextures.com/excelfiles.html#Function

under the subheading: Extract List of Unique Items

 

[Aladin Akyurek]

Debra,

I'd like to suggest two small modifications...

You can add a column to the left of the column of duplicate data. With
your data starting in cell B2, enter the following formula in cell A2:
=IF(COUNTIF(B$1:B2,B2)=1,MAX(A$1:A1)+1,"")

Copy this down to the last row of data.

In a column to the right, enter the following formula in row 2:
=IF(MAX(NameCount)<ROW(1:1),"",VLOOKUP(ROW(1:1),NameList,2))

Lets suppose that this formula will be entered in G2...

=IF(MAX(NameCount)<ROW()-ROW($G$2)+1,"",VLOOKUP(ROW()-ROW($G$2)+1,NameList,2
))