Unique List of Names in list

[Bundy]

The Below statement should work with what I need, but is there a way of removing the spaces in the Unique1 list? I'm using the unique list for a dropbox & it hates spaces.

=IF(COUNTIF(INDIRECT(ADDRESS(ROW(List1),COLUMN(List1),1)&":"&ADDRESS(ROW(OFFSET(List1,ROW()-ROW(Unique1),0,1,1)),COLUMN(List1),4)),INDIRECT(ADDRESS(ROW(OFFSET(List1,ROW()-ROW(Unique1),0,1,1)),COLUMN(List1))))=1,INDIRECT(ADDRESS(ROW(OFFSET(List1,ROW()-ROW(Unique1),0,1,1)),COLUMN(List1))),"")

http://www.cpearson.com/excel/ListFunctions.aspx

 

[Domenic]

Assuming that A2:A11 contains the data, try...

B2:

=SUM(IF(FREQUENCY(IF(A2:A11<>"",MATCH("~"&A2:A11,A2:A11&"",0)),ROW(A2:A11)-ROW(A2)+1),1))

...confirmed with CONTROL+SHIFT+ENTER

C2, copied down:

=IF(ROWS(C$2:C2)<=$B$2,INDEX($A$2:$A$11,SMALL(IF($A$2:$A$11<>"",IF(MATCH("~"&$A$2:$A$11,$A$2:$A$11&"",0)=ROW($A$2:$A$11)-ROW($A$2)+1,ROW($A$2:$A$11)-ROW($A$2)+1)),ROWS(C$2:C2))),"")

......confirmed with CONTROL+SHIFT+ENTER

Hope this helps!

 

[Bundy]

Ta! Making a unique list should be a built in excel function - I'm sure many people use it!

 

[Domenic]

Actually, the Advanced Filter can be used to return a list of unique values. Select 'Unique records only'.

 

[Bundy]

Advanced Filter was what I was looking at, but needed this list to update each time the sheet was opened.