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.

[TABLE="class: grid, width: 128"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]

David​

[/TD]
[TD="bgcolor: transparent"]David[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Jack[/TD]
[TD="bgcolor: transparent"]Jack[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]David[/TD]
[TD="bgcolor: transparent"]Angelica[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Jack[/TD]
[TD="bgcolor: transparent"]Peter[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Angelica[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Peter[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

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.