#### jeffreyweir

##### Active Member

**Formula Challenge 007 - return distinct/duplicate /unique values as an array**

Sample Data

**Challenge Description**

Good evening, Gentlemen. Your mission, should you choose to accept it, is to:

- Write three formula that respectively return the distinct, duplicate, and unique values of a numerical range.

- The output of each formula must be an array that can be used

- Each formula must return an array of the exact size containing only the distinct, duplicate, and unique values. So as per the sample data and output below:

* The array produced by the Distinct formula will be exactly 13 elements long

* The array produced by the Unique formula will be exactly 9 elements long

* The array produced by the Duplicate formula will be exactly 5 elements long

- Each formula should be completely dynamic, and able to be used on a 1D array of any size.

This message will self-destruct in 10 seconds.

Good LuckGood evening, Gentlemen. Your mission, should you choose to accept it, is to:

- Write three formula that respectively return the distinct, duplicate, and unique values of a numerical range.

- The output of each formula must be an array that can be used

*directly by other formula (such as Max, Min, Average etc). That is, we're after a single formula that you can directly incorporate into other formulas - not*a formula that has to be entered over a worksheet range in order to work.- Each formula must return an array of the exact size containing only the distinct, duplicate, and unique values. So as per the sample data and output below:

* The array produced by the Distinct formula will be exactly 13 elements long

* The array produced by the Unique formula will be exactly 9 elements long

* The array produced by the Duplicate formula will be exactly 5 elements long

- Each formula should be completely dynamic, and able to be used on a 1D array of any size.

This message will self-destruct in 10 seconds.

Good Luck

Sample Data

[pre]

Code:

```
Data Distinct Unique Duplicate
1 0 1 1
2 1 15 2
45 2 22 3
45 3 23 45
49 15 35 49
35 22 37
2 23 39
15 35 41
22 37 99
49 39
39 41
23 49
41 99
3
37
1
3
2
99
0
```