# Getting unique values in Excel by using formulas only

###### Posted By: Anonymous

Do you know a way in Excel to “calculate” by formula a list of unique values ?

**E.g:** a vertical range contains values ** "red"**,

**,**

`"blue"`

**,**

`"red"`

**,**

`"green"`

**,**

`"blue"`

`"black"`

and I want to have as result

**,**

`"red`

**,**

`"blue"`

**,**

`"green"`

**+ eventually 2 other blank cells.**

`"black"`

I already found a way to get a calculated sorted list using SMALL or LARGE combined with INDEX, but I’d like to have this calculated sort as well, WITHOUT USING VBA.

## Solution

This is an oldie, and there are a few solutions out there, but I came up with **a shorter and simpler formula** than any other I encountered, and it might be useful to anyone passing by.

I have named the colors list `Colors`

(A2:A7), and the **array formula** put in cell **C2** is this (**fixed**):

```
=IFERROR(INDEX(Colors,MATCH(SUM(COUNTIF(C$1:C1,Colors)),COUNTIF(Colors,"<"&Colors),0)),"")
```

Use `Ctrl+Shift+Enter`

to enter the formula in **C2**, and copy C2 down to **C3:C7**.

Explanation with sample data {“red”; “blue”; “red”; “green”; “blue”; “black”}:

`COUNTIF(Colors,"<"&Colors)`

returns an array (#1) with the count of values that are smaller then each item in the data {4;1;4;3;1;0} (black=0 items smaller, blue=1 item, red=4 items). This can be translated to a**sort value**for each item.`COUNTIF(C$1:C...,Colors)`

returns an array (#2) with 1 for each data item that is already in the sorted result. In C2 it returns {0;0;0;0;0;0} and in C3 {0;0;0;0;0;1} because “black” is first in the sort and last in the data. In C4 {0;1;0;0;1;1} it indicates “black” and all the occurrences of “blue” are already present.- The
`SUM`

returns the**k-th**sort value, by counting all the smaller values occurrences that are already present (sum of array #2). `MATCH`

finds the first index of the k-th sort value (index in array #1).- The
`IFERROR`

is only to hide the`#N/A`

error in the bottom cells, when the sorted unique list is complete.

To know how many unique items you have you can use this **regular formula**:

```
=SUM(IF(FREQUENCY(COUNTIF(Colors,"<"&Colors),COUNTIF(Colors,"<"&Colors)),1))
```

###### Answered By: Anonymous

Disclaimer: This content is shared under creative common license cc-by-sa 3.0. It is generated from StackExchange Website Network.