# 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

• 8.5 KB Views: 8

#### deciog

##### Active Member
Johnny3000

Check if this is how you want it

Decio

#### Attachments

• 10.1 KB Views: 10

#### Johnny3000

##### 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.
Solved!

#### bosco_yip

##### Excel Ninja
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

• 10.3 KB Views: 4

#### p45cal

##### 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.

#### deciog

##### 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

Decio

#### Hans Knudsen

##### New Member
@Peter Bartholomew

Peter would you care to explain why =FILTER(numbers, numbers) omit 0s?

#### 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.

#### Hans Knudsen

##### New Member
Peter - I understand, thank you.