• 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

    Hui...

  • 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

Johnny3000

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!
 

Attachments

  • 02_02_2020.xlsx
    8.5 KB · Views: 9
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.
Solved!
 
Non-array formula for distinct values from column H

In K2, copied down :

=IFERROR(AGGREGATE(15,6,IF(FREQUENCY((H$2:H$100<>"")*H$2:H$100,$H$2:H$100),H$2:H$100,""),ROW(A1)),"")

Regards
Bosco
 

Attachments

  • distinct values (BY).xlsx
    10.3 KB · Views: 8
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:
65316
 
'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.
 
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

Decio
 
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 …​
 
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.
 
Back
Top