0

I have a query, could anybody please help. My query is, i want to extract distinct/unique values from an array. For example: my array is {A,A,A,B,B,C,C,C,D,D}, the resultant should also be an array but only with distinct values {A,B,C,D}. Only excel formula required, i already have a function written but UDF"s cannot be used in data validation list, so cant use it. Can anyone help?

Gaurav
  • 1
  • 1
  • 1
    Hi, Welcome to SO. I would suggest you add that UDF. It will give a starting point for any answer... For unique values in VBA array [refer this link](https://stackoverflow.com/a/3017973/9808063) – Naresh May 18 '20 at 22:58
  • Also, see [this link](https://stackoverflow.com/questions/61837696/excel-extract-substrings-from-string-using-filterxml/61837697?r=SearchResults&s=2|0.0000#61837697) for extracting unique values using FILTERXML Function. – Naresh May 18 '20 at 23:13
  • Where is that data? Is it in VBA? Is it in the worksheet? – teylyn May 18 '20 at 23:53
  • So, basically my problem is that i have extracted a list that contains duplicates. This list is being passed on to data validation list argument to make use of drop down functionality. Since, this list is not a unique value list, it repeats which i want to avoid. i hope you got my query. – Gaurav May 19 '20 at 05:23

1 Answers1

3

If the data is in a worksheet, you can use the Unique() function (available only in Excel 365) to create a list of unique values to use in a Data Validation source.

Formula in B2 (note that it spills down automatically, without dragging down)

=UNIQUE(A2:A11)

enter image description here

The Data Validation can then refer to 

=$B$2#

The hash symbol is the syntax if the whole spill range should be used.

enter image description here

And the drop-down in action. No VBA. Just formulas. (Note that the data source, the Unique() function and the data validation cells can all be on different worksheets.

enter image description here

teylyn
  • 34,374
  • 4
  • 53
  • 73
  • Hi Teylyn, i dont have access to unique function as of now. also, the sheet being prepared will be used by people who will be working on older versions of excel, so i would prefer not using unique. kindly let me know if you have any other option available. – Gaurav May 19 '20 at 05:25
  • There are many, many sources on the internet that describe how to get unique values from a list. Which approach did you use and where are you stuck? Show your own effort and the we can help you with that. – teylyn May 19 '20 at 21:27