# Excel Unique Data Vs Excel Distinct Data

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

Reading various forums I notice conflict over the definition of **Unique and Distinct values** or data, it spurred me to write this tutorial. Reading various forums and tutorials I noticed that the definition of **Unique and 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

- data which appear
**only once**(**no duplicates**) - data that appear
**more than once**(**with duplicate**s)

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 data or values** to the Advanced Filter option (see figure below).

# Advanced Filter in the service of a copy of unique 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 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 Data In Cell Range (All Unique Data)

**Unique values are different from values Unique Distinct**. In the example above **I showed you the Unique values** that we get by using the Advanced Filter. List of Unique value 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 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 values**. Note that some formulas automatically sort values asc A-Z.

Figure 2.

## Copy Unique Values Can Be Repeated

The formulas in cells 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 Values From Range

If you need to create a list of unique values from a range of cells or more columns then use the formula below. This formula is copied from source www.extendoffice.com

**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");)

Figure 3.

# UNIQUE DISTINCT Data In a Range Of Cells

(Unique Distinct Data Only)

**Unique Distinct 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))

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 Distinct 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 Distinct 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;""))

**UNIQUE**data = data appear one or more times**DISTINCT**data = appear only once

## Copy Unique values using VBA macro

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