Removing Duplicates through VBA throwing pop-up

[iPenguin]

Column C in my spreadsheet contains values that will be client-chosen and frequently updated. I want column D to have data validation applied dynamically that pulls from that list. However, it needs to contain alphabetically ordered, unique values.

What I am currently doing is using the following formula to alphabetically order those values in a hidden column (BK). (Note: the site I found this on indicated it should only show unique values, however it did not).

{=INDEX(List,MATCH(0,IF(MAX(NOT(COUNTIF($BK$15:BK15,List))*(COUNTIF(List,">"&List)+1))=(COUNTIF(List,">"&List)+1),0,1),0))}

To update column D dynamically, I am using the following code:

Please Login or Register  to view this content.

This seems to work, except every time I click in a cell in column D it still throws a pop up box called "Remove Duplicates" that shows two checked checkboxes -- "Select All" and "Column BL". It also tells me how many duplicates were found and how many unique values will remain.

I am at a loss for why displayalerts=false hasn't turned this off, but it definitely isn't an option to have this fire every time someone clicks in column D. Has anyone seen this before? (I am on Excel for Mac 2016 by the way).

[iPenguin]

I should note that I am also open to alternative suggestions for how to update these validation lists based on other values if my method of copy/paste/validate is needlessly complex.

I have additional scenarios where rather than adding validation lists based on another column in the same sheet, I am actually searching over values on another sheet. If I could pull those values directly into the validation list without having to do the Find/Copy/Paste/Validate dance, I would be thrilled but I am not sure how. (See below for how I am actually addressing that scenario):

Please Login or Register  to view this content.