# Excel 'Unique Data' Vs 'Unique Distinct' Data

## The Difference Between "Unique" and "Unique Distinct" in Excel

Reading various forums I notice conflict over the definition of '**Unique' and 'Unique Distinct' values** or data, it spurred me to write this tutorial. Reading various forums and tutorials I noticed that the definition of '**Unique values' and 'Unique Distinct values' or data very different from person to person** that was involved in a topics on a forums, blogs or websites. I will in this tutorial to give my view definition and you decide whom you trust.

Through continuous work in Excel and reading the various forums on the Internet, I noticed that many users of Excel, so I'm using the name **Unique items** for information or the values that must be allocate in a list and actually used one name (Unique) for the two types of data, was "**unique**" or "**distinct**" data (or values in a range of cells). **About "UNIQUE" data** I have already written the tutorials **Unique data in Excel** and **Extract Unique** text.

In this tutorial you're reading, I will try to show that difference. '**Unique vs Unique Distinct data'**. Some may wonder, why I use the term "**Unique**". Because I'm such a novice entered to use Excel with no understanding. **This tutorial is a collection of formulas for UNIQUE data that is collected from the Internet and all the merits of the individual authors**.

Excel example:

Column 'A' there are some data (text/values). These data or values may appear as follows

**Distinct values/data**are those that occur at least once in the dataset. So if a name appears two or three times, itâ€™s still counted as one distinct name. This can be achieved by removing the duplicate values/names and keeping all the distinct ones.**Unique values/data**are those that only occur once. Unique values are the values that exist in a list only once. This means that all the names that repeat and have duplicates are not unique.

There are two types of data that can be extracted from column 'A'. These are the '**Unique**' and '**Unique Distinct**' data or values.

## How to know which are and which are 'Unique' or 'Unique Distinct' values?

Easy. In Excel, you have a built-in option that allows **Excel automatically allocate 'Unique Distinct' data or values** to the Advanced Filter option (see figure below).

# Advanced Filter in the service of a copy of Unique Distinct' values

So, we're going to take advantage of Excel's option allocations **Unique value using the Advanced Filter**. Copy the unique values in column 'A' can do using advanced filter as follows (Figure 1).

- Select the values in column 'A'
- On the ribbon tab, click Advanced DATA (group Sort & Filter)
- On the pop-up dialog box "Advanced Filter", select the radio button "Copy to another location"
- Enter the address of the first cell where you will be singled out or copy a single value,
- Be sure to include the "Unique records only", you're done click on the OK button
- Now you have a column in the 'C' have a value; 100, 10, 50, 20, 325, 700, 40, 60 (you note that a total of 8 values)

So, **Excel is copied every value that occurs at least once**, duplicates ignored. If we believe Microsoft developers who programmed Excel then for **UNIQUE value** or data then the following definitions apply.

**"Unique Distinct" data**, that all data from a range of cells that appear at least once regardless of whether they are duplicated or not. **List 'Unique' data does not include duplicates**.

Figure 1.

# UNIQUE DISTINCT Data In Cell Range (All Unique Data)

**UNIQUE DISTINCT values are different from UNIQUE values**. In the example above **I showed you the Unique Distinct values** that we get by using the Advanced Filter. List of Unique Distinct values in the range of cells is to copy all the values (data) **that appear at least once and can be repeated**. In the picture below you see ways in which we **can copy UNIQUE DISTINCT value of a range of cells**. As you can see the values are in column 'A'. In the other columns are formulas by which we **can copy the UNIQUE DISTINCT values**. Note that some formulas automatically sort values asc A-Z.

Figure 2.

## Copy 'Unique Distinct' values, Unique data which can be repeated

The formulas in cells (Figure 2.) in the image above are the following:

**The formula in cell 'C2'** is the following:

This is a ARRAY formula, you **should finish with Ctrl+Shift+Enter**. Formula sorts values Ascending.

=INDEX($A$2:$A$12;MATCH(0;COUNTIF($A$2:$A$12;"<"&$A$2:$A$12)-SUM(COUNTIF($A$2:$A$12;"="&C$1:C1));0))

**The formula in cell 'E2'** is the following:

This is a **ARRAY formula**, you should finish with **Ctrl+Shift+Enter**. Formula sorts values Ascending.

=SMALL(IF(FREQUENCY($A$2:$A$12;$A$2:$A$12);$A$2:$A$12);ROWS($1:1))

**The formula in cell 'G2'** is the following:

This is a **ARRAY formula**, you should finish with **Ctrl+Shift+Enter**.

=INDEX($A$2:$A$12;MATCH(0;FREQUENCY(IF(EXACT($A$2:$A$12;TRANSPOSE($G$1:G1));MATCH(ROW($A$2:$A$12);ROW($A$2:$A$12));"");MATCH(ROW($A$2:$A$12);ROW($A$2:$A$12)));0))

**The formula in cell 'I2'** is the following:

This is a **ARRAY formula**, you should finish with **Ctrl+Shift+Enter**. This formula works well in cases where the values are between empty cells.

=INDEX($A$2:$A$12;MATCH(1;INDEX((COUNTIF($I$1:I1;$A$2:$A$12)=0)*($A$2:$A$12<>"");0);0))

**The formula in cell 'K2'** is the following:

This is a **ARRAY formula**, you should finish with **Ctrl+Shift+Enter**. This formula works well in cases where the values are between empty cells.

=INDEX($A$2:$A$12;MATCH(0;IF(ISBLANK($A$2:$A$12);1;COUNTIF($K$1:K1;$A$2:$A$12));0))

**The formula in cell 'M2'** is the following:

This is a **ARRAY formula**, you should finish with **Ctrl+Shift+Enter**.

=INDEX($A$2:$A$12;MATCH(0;COUNTIF($M$1:M1;$A$2:$A$12);0))

## Copy "Unique Distinct" / "Unique" Values From Range

Please see Figure 3. If you need to return a list of **unique distinct** values or **unique** values only **from a range** of cells or **multiple columns** then use the formula below. This formula is copied from source www.extendoffice.com

'E' column - UNIQUE DISTINCT values from range

**The formula for Text**

=INDIRECT(TEXT(MIN(IF(($A$2:$C$6<>"")*(COUNTIF($E$1:E1;$A$2:$C$6)=0);ROW($2:$6)*100+COLUMN($A:$C);7^8));"R0C00");)&""

**The formula for Values**

=INDIRECT(TEXT(MIN(IF(($A$2:$C$6<>"")*(COUNTIF($E$1:E1;$A$2:$C$6)=0);ROW($2:$6)*100+COLUMN($A:$C);7^8));"R0C00");)

or ARRAY formula below

=IFERROR(INDEX($A$1:$C$6;SMALL(IF(($A$2:$C$6<>"")*ISERROR(MATCH($A$2:$C$6;$E$1:$E1;0));ROW($A$2:$C$6));1);MOD(SMALL(IF(($A$2:$C$6<>"")*(ISERROR(MATCH($A$2:$C$6;$E$1:$E1;0)));ROW($A$2:$C$6)+COLUMN($A$2:$C$6)/100);1)*100;100));"")

'G' column - UNIQUE values from range

In the picture below, notice the difference in the results shown in the 'E' and 'G' columns. In the 'G' column shows only the unique values are not repeated. So the ARRAY formula in the 'G2' cell (in Figure 3) is as follows:

=IFERROR(INDEX($A$2:$C$6;MIN(IF((COUNTIF($A$2:$C$6;$A$2:$C$6)=1)*(COUNTIF(K$1:$K1;$A$2:$C$6)<>1);ROW($A$2:$C$6)-MIN(ROW($A$2:$C$6))+1));MATCH(FALSE;COUNTIF($K$1:K1;INDEX($A$2:$C$6;MIN(IF((COUNTIF($A$2:$C$6;$A$2:$C$6)=1)*(COUNTIF(K$1:$K1;$A$2:$C$6)<>1);ROW($A$2:$C$6)-MIN(ROW($A$2:$C$6))+1));;1))+COUNTIF($A$2:$C$6;INDEX($A$2:$C$6;MIN(IF((COUNTIF($A$2:$C$6;$A$2:$C$6)=1)*(COUNTIF(K$1:$K1;$A$2:$C$6)<>1);ROW($A$2:$C$6)-MIN(ROW($A$2:$C$6))+1));;1))<>1;0);1);"")

Figure 3.

# UNIQUE Data In a Range Of Cells (Unique Data Only)

**"UNIQUE" data**, that is **data that appear only once in the range of cells**, this **data is not repeated**. When these data are extracted then we create a list of all the values that **appear in the range of cells only once and do not have duplicates**.

You Note!

In the image below extracted or copied values from column 'A'. Unlike the data in the Figure2, note that here we have 6 values copied. So, we have separated all of the **values that appear only once and do not have duplicates**.

Figure 4.

The formulas in cells in the image above are the following:

**The formula in cell 'C2'** is the following:

This is **ARRAY formula**, you should finish with **Ctrl+Shift+Enter**. (enter formula in one line)

=IF(COUNT(IF(COUNTIF($A$2:$A$12;$A$2:$A$12)=1;ROW($A$2:$A$12)-ROW($A$2)+1;""))<ROW()-1;

"";INDEX($A$2:$A$12;SMALL(IF(COUNTIF($A$2:$A$12;$A$2:$A$12)=1;ROW($A$2:$A$12)-ROW($A$2)+1;"");ROW()-1)))

**The formula in cell 'E2'** is the following:

This is **ARRAY formula**, you should finish with **Ctrl+Shift+Enter**.

=INDEX($A$2:$A$12;MATCH(0;INDEX(COUNTIF($E$1:E1;$A$2:$A$12)+(COUNTIF($A$2:$A$12;$A$2:$A$12)<>1);0;0);0))

SUM UNIQUE values.

The cell 'G2' is ARRAY formula that sums only on information that is one-occurring and no duplicates.

=SUM(IF(COUNTIF(A2:A12;A2:A12)=1;A2:A12;""))

COUNT UNIQUE values

In cell 'G7' there is an ARRAY formula that counts how many times a unique value (which does not repeat)

=COUNT(IF(COUNTIF(A2:A12;A2:A12)=1;ROW(A2:A12)-ROW(A2)+1;""))

If you use Excel 2013 also look at **how to count Distinct values using PowerPivot** in the tutorials on Internet

## If You Need Count Unique Values

The following formula (Figure 2) that counted Unique data/value

(The formula below counts numeric values. This is **ARRAY formula (CSE formula)**, you should finish with **Ctrl+Shift+Enter**.)

=SUM(IF(FREQUENCY(A2:A12;A2:A12)>0;1))

(If there are negative numbers or zero (0), the formulas below them will not count)

=SUM(--(FREQUENCY(A1:A12;A1:A12)>0))

or

=SUMPRODUCT((A2:A12<>"")/COUNTIF(A2:A12;A2:A12&""))

(The formula below will not work well if there are blank cells between values)

=SUMPRODUCT(1/COUNTIF(A2:A12;A2:A12))

(The formula below is **ARRAY formula** that you need to finish with Ctrl+Shift+Enter.)

=SUM(--(FREQUENCY(IF(A2:A12<>"";MATCH("~"&A2:A12;A1:A12&"";0));ROW(A2:A12)-ROW(A1)+1)>0))

(The formula below counts alphanumeric data and ignores empty cells. This is **ARRAY formula**, you should finish with Ctrl+Shift+Enter.)

=SUM(IF(FREQUENCY(IF(LEN(A2:A12)>0;MATCH(A2:A12;A2:A12;0);"");IF(LEN(A2:A12)>0;MATCH(A2:A12;A2:A12;0);""))>0;1))

(The formula below will not work well if there are blank cells between values. This is **ARRAY formula**, you should finish with Ctrl+Shift+Enter.)

=SUM(IF(FREQUENCY(MATCH(A2:A12;A2:A12;0);ROW(A2:A12)-ROW(A1)+1);1))

(The formula below will not work well if there are blank cells between values. This is ARRAY formula you should finish with Ctrl+Shift+Enter.)

=SUM(1/COUNTIF(A2:A12;A2:A12))

## Summation Unique Values

The formula in cell O2 (Figure 2) **SUM all Unique Values**. This is **ARRAY formula** that you need to finish with Ctrl+Shift+Enter. The formula will not work well if there are blank cells between values!

=SUM(IF(IF(MATCH(A2:A12;A2:A12;0)=(ROW(A2:A12)-ROW(A2)+1);(ROW(A2:A12)-ROW(A2)+1);0)<>0;A2:A12;""))

## Count Unique Values

The formula in cell 'G7' (Figure 4) **Count the Distinct Values**. This is **ARRAY formula**, that you need to finish with Ctrl+Shift+Enter.

=COUNT(IF(COUNTIF(A2:A12;A2:A12)=1;ROW(A2:A12)-ROW(A2)+1;""))

## SUM UNIQUE Values

**The formula in cell 'G2'** (Figure 4) SUM the Distinct values. This is **ARRAY formula**, that you need to finish with Ctrl+Shift+Enter.

=SUM(IF(COUNTIF(A2:A12;A2:A12)=1;A2:A12;""))

**Conclusion:****UNIQUE DISTINCT data** = appear once or more times but only one data is returned as a result**UNIQUE data** = appear only once and have no duplicates

## Copy Unique Distinct values using VBA macro

(https://www.listendata.com/2013/05/excel-3-ways-to-extract-unique-values.html)

Sub UniqueListFromColumn()

Dim lastrow As Long

lastrow = Cells(Rows.Count, "A").End(xlUp).Row 'source column

ActiveSheet.Range("A2:A" & lastrow).AdvancedFilter _ 'source range

Action:=xlFilterCopy, _

CopyToRange:=ActiveSheet.Range("B2"), _ 'destination first cell

UNIQUE:=True

End Sub

An Excel example of how to choose a formula for **Counting Unique Values**.

Image source idea (excelhowto.com). Formulas can be copied from forums/blogs/webs (mrexcel.com, vertex42.com, excelxor.com, trupmexcel.com, excel-easy.com, myonlinetraininghub.com, msofficeforums.com, get-digital-help.com, mbaexcel.com, excelforum.com, extendoffice.com, excelhowto.com, ozgrid.com, ecelguru.ca, etc internet...). Also, see tutorial UNIQUE in Excel.

Also you can se Excel example tutorial Count Unique From Multiple Columns If Match Criteria

**Pay attention**, you may need as a separator of arguments formula **use a comma (,) instead of a semicolon (;)**.

Copy formula to Notepad and with Find/Replace change semicolon to comma.