## The difference between "Unique" data and "Distinct" data in Excel

Reading various forums I notice conflict over the definition of

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 "

example:

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

**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 topic on a forum, blog or website. 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.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 duplicates)**

There are two types of data that can be extracted from column 'A'. These are the '

How to know which are and which are

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

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

Figure 1.

**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 an ARRAY formula, you should finish with

=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 an 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 an 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 an 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 an 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 an 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))

The formula in cell C2 is the following: This is an 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 an 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 an 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 an 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 an 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 an 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 the 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 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.

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 image 2, note that here we have six 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

=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

The formula in cell C2 is the following: This is ARRAY formula, you should finish with

**Ctrl+Shift+Enter**.=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 tutorial on link http://datapigtechnologies.com/blog/index.php/distinct-count-in-pivot-tables-finally-in-excel-2013/## Count Unique values

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

(The formula below counts numeric values. This is ARRAY 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))

(The formula below counts numeric values. This is ARRAY 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;""))

=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(IF(COUNTIF(A2:A12;A2:A12)=1;ROW(A2:A12)-ROW(A2)+1;""))

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

**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 = date appear one or more times**

DISTINCT data = appear only onceDISTINCT 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

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

See also the tutorial www.cpearson.com/excel/distinctvalues.aspx

Also, see tutorial UNIQUE in Excel

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

See also the tutorial www.cpearson.com/excel/distinctvalues.aspx

Also, see tutorial UNIQUE in Excel