Define name is name manager without duplicate

[proexcel]

Hello to all
I have this names in one column
David
Jack
David
Jack
Angelica
Peter
I want to write a formula in name manager that automatically remove duplicate names and give me a unique list with offset formula, I mean that I want to automatically increase or decrease the length of the list with add name or remove it but unique list without reputation.

 

[Aladin Akyurek]

Let column A of Sheet1 houses the data.

David​	David
Jack	Jack
David	Angelica
Jack	Peter
Angelica
Peter

<tbody>
</tbody>

1. Define Names in the Formula Manager as referring to:

=Sheet1!$A$2:INDEX(Sheet1!$A:$A,MATCH(REPT("z",255),Sheet1!$A:$A))

2. Define Ivec in the Formula Manager as referring to:

=ROW(Names)-ROW(INDEX(Names,1,1))+1

3. Define DistinctNamesCount in the Formula Manager as referring to:

=SUM(IF(FREQUENCY(IF(1-(Names=""),MATCH(Names,Names,0)),Ivec),1))

4. In B2 control+shift+enter, not just enter, and copy down:

=IF(ROWS($B$2:B2)>DistinctNamesCount,"",
    INDEX(Names,SMALL(IF(FREQUENCY(IF(1-(Names=""),MATCH(Names,Names,0)),Ivec),Ivec),
    ROWS($B$2:B2))))

5. Define DistinctNames in the Formula Manager as referring to:

=OFFSET(Sheet1!$B$2,0,0,DistinctNamesCount)

The latter gives you what you are asking for.