• 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


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

How to get distinct values


New Member
Hi everyone,

let's say I have some (numeric) values in column "H" on sheet 1 that repeat themselves.

I would like to see in column "K" distinct values (in this case numbers 0, 6, 15 (in ascending order)).

Please see the file in attachment. It will be probably clear.

Thank you my friends!



New Member
Thank you very much, that is exactly what I need!

It's an array formula, but I think I know how to work with them.


Well-Known Member
And a no-formula approach:
Select a cell in the list, Insert|Pivot table, locate it at K1, take the single Numbers field and put it into the Rows area:

Peter Bartholomew

Well-Known Member
'No array formula' never had much appeal to me as a concept; I had schooled myself to commit formulas using CSE by default, but I would gladly have swapped the default so that Enter gave arrays, whilst Ctrl+Enter was required to perform the extra step of breaking ranges up using implicit intersection. Now dynamic arrays make array formulas the path of least resistance. In this case
= UNIQUE(numbers)
does the job as requested but if one wishes to remove 0s and sort
= SORT( UNIQUE( FILTER(numbers, numbers) ) )
does a good job.


Active Member
Johnny3000, Good Morning.

My Excel is OFFICE 365, this version does not need CTRL + SHIFT + ENTER, I forgot to inform you what previous version you need


Marc L

Excel Ninja
I would like to see in column "K" distinct values (in this case numbers 0, 6, 15 (in ascending order)).
Hi, just using an easy Excel Basics advanced filter you can directly create this list then you can apply a sort …​

Peter Bartholomew

Well-Known Member
Hello Hans, good to hear from you.
The first parameter is simply the range/array to be filtered.
The second is a (Boolean) criterion for the filtering.
Zero counts as FALSE and so is omitted; any non-zero number counts as TRUE so is retained.
= FILTER( numbers, numbers<>0 )
might have been clearer.