Duplicate Values in Data Validation

Hello, how do I remove the duplicates in the data validation on column B?

I read about =SORT(UNIQUE for O365 however that's not showing up for me. (Maybe because I am using O365 Plus Pro?)

Attached is the sample file.

thank you

Some reading

https://www.myonlinetraininghub.com/...ic-unique-list

https://www.myonlinetraininghub.com/...dynamic-arrays


Especially

Quote:

---

Data Validation

Now that we have our nicely sorted unique list it makes sense that you might want to use it in a data validation list. To reference a spilled array we simply use the new Spilled Range Operator, #, as shown in the Data Validation ‘Source’ field below:

---

The above approach requires a 'sort of' 'helper' column


A more involved approach that doesn't require the helper column

http://www.vbaexpress.com/forum/show...rkbook-Startup

Hi Paul, I have a few questions:
1: what are _xlfn and _xlws? Are they named ranges? Why can't I find them in the Name Manager?
2. how come sort and unique are working with those two but I can't make it work? :rotlaugh:
3. why can't I click on the dropdown list in C2?

I posted in the thread instead of here :rotlaugh::rotlaugh:

Quote:

---

Originally Posted by Paul_Hossler

A more involved approach that doesn't require the helper column

http://www.vbaexpress.com/forum/show...rkbook-Startup

---

Hmmm I'm not sure how I'll be able to apply this to mine because I need to use it in multiple rows in the same column :(

Hidden names

Code:

---

Option Explicit


Sub unhideAllNames()
    Dim tempName As Excel.Name
   
    For Each tempName In ActiveWorkbook.Names
        tempName.Visible = True
    Next
End Sub

---

Oh okay so they're hidden.. Why am I unable to click on the dropdown list?

Attachment 26562

Using Example_1.xlsm, does the list (above) look the same?

What happens when you try the dropdown list on the other sheet?

Attachment 26564

Oh let me try