Returning a Unique Result Set from a List

  • Thread starter Thread starter Mark T
  • Start date Start date
M

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!
 
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?
 
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
 
Debra,

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

Debra Dalgleish said:
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,"")
Click to expand...

=IF((B2 said:
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))
Click to expand...

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
))
 
Back
Top