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?
4
-
1Hi, 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 linkNaresh– Naresh2020-05-18 22:58:07 +00:00Commented May 18, 2020 at 22:58
-
Also, see this link for extracting unique values using FILTERXML Function.Naresh– Naresh2020-05-18 23:13:25 +00:00Commented May 18, 2020 at 23:13
-
Where is that data? Is it in VBA? Is it in the worksheet?teylyn– teylyn2020-05-18 23:53:10 +00:00Commented May 18, 2020 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– Gaurav2020-05-19 05:23:22 +00:00Commented May 19, 2020 at 5:23
Add a comment
|
1 Answer
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)
The Data Validation can then refer to
=$B$2#
The hash symbol is the syntax if the whole spill range should be used.
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.
2 Comments
Gaurav
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.
teylyn
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.