Data validation to list unique values

[Syedali]

Hi,

I want use data validation to list the name but unique values alone using formula.

Attached example for you reference.

 

[John Jairo V]

Hi, Syedali!

Check file with two options. Both with Helper columns.

1. C column (NameHelper). Formula:
[C2] : =IFERROR(INDEX(A$2:A$10,MATCH(,INDEX(COUNTIF(C$1:C1,A$2:A$10),),)),"")

And create in Name Manager (Name):
=Sheet1!$C$2:INDEX(Sheet1!$C:$C,COUNTIF(Sheet1!$C:$C,"?*"))

2. D column (SortNameHelper). Formula:
[D2] : =IFERROR(INDEX(A$2:A$10,MATCH(,INDEX(COUNTIF(A$2:A$10,"<"&A$2:A$10)-SUMPRODUCT(COUNTIF(A$2:A$10,D$1:D1)),),)),"")

And create in Name Manager (SortName):
=Sheet1!$D$2:INDEX(Sheet1!$D:$D,COUNTIF(Sheet1!$D:$D,"?*"))

Check file. Blessings!

 

[Syedali]

Hi, Syedali!

Check file with two options. Both with Helper columns.

1. C column (NameHelper). Formula:
[C2] : =IFERROR(INDEX(A$2:A$10,MATCH(,INDEX(COUNTIF(C$1:C1,A$2:A$10),),)),"")

And create in Name Manager (Name):
=Sheet1!$C$2:INDEX(Sheet1!$C:$C,COUNTIF(Sheet1!$C:$C,"?*"))

2. D column (SortNameHelper). Formula:
[D2] : =IFERROR(INDEX(A$2:A$10,MATCH(,INDEX(COUNTIF(A$2:A$10,"<"&A$2:A$10)-SUMPRODUCT(COUNTIF(A$2:A$10,D$1:D1)),),)),"")

And create in Name Manager (SortName):
=Sheet1!$D$2:INDEX(Sheet1!$D:$D,COUNTIF(Sheet1!$D:$D,"?*"))

Check file. Blessings!

Hi,

Thanks this is nice one....

But i know this solution using helper column.

I need to know any possibility of getting Data Validation without helper column.

Am faced on interview with this question ...