• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Unique Values in drop down

rupal1234

Member
NAMES
AANCHAL
RUPAL
RAHUL
RAM
SHYAM
RUPAL
RAHUL
AANCHAL

Here i want unique values in drop down like non repeated values in dropdown.
For eg. if AANCHAL is coming two times then it would show only one time in my dropdown
 

Attachments

  • unique values.xlsx
    11.9 KB · Views: 3
Hi @rupal1234 ,

You can do many Way ?

Method 1 :

=K4 Copy Down formula
=IFERROR(INDEX($A$4:$A$12,MATCH(0,COUNTIFS($K$4:K4,$A$4:$A$12),0)),"")

Then Use offset Formula for Dynamic range for Unique Names

Go To Name Manager > New > Given Name > Copy Below Formula > ok > Close
=OFFSET(Sheet1!$K$5,,,MATCH("",Sheet1!$K$5:$K$13,0)-1,)

Then Go to Data validation > Allow Select List > Press F3 >Source :=Select Name Mangar Name > Ok


Method 2 : Using Power Query

1) Convert Input Names Range To Table ( Ctrl + T )
2) Go to Power Query Tab > From Table Or Range Select > Power Query Window Will Appeared > Home Tab > Remove Rows > Remove Duplicate > Close & Load

3)Unique Name only visible only


Method 3 : Using Unique Function ( Only For Office 365 or Office 2019)
=UNIQUE(A5:A12)


Regard
Rahul shewale
 

Attachments

  • unique values.xlsx
    21.6 KB · Views: 7
Back
Top