# Unique List of Names in list

#### Bundy

##### New Member
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.

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

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

#### Domenic

##### MrExcel MVP
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

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

#### Domenic

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

#### Bundy

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

Replies
1
Views
167
Replies
31
Views
727
Replies
13
Views
403
Replies
14
Views
440
Replies
1
Views
186

1,191,371
Messages
5,986,264
Members
440,015
Latest member
knijgh

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back