Our Recommendation for You Search your Query, You can find easily. for example search by book name or course name or any other which is related to your education

Followers

Unique vs Distinct

The difference between "Unique" data and "Distinct" data 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 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 '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).
  1. Select the values in column 'A'
  2. On the ribbon tab, click Advanced DATA (group Sort & Filter)
  3. On the pop-up dialog box "Advanced Filter", select the radio button "Copy to another location"
  4. Enter the address of the first cell where you will be singled out or copy a single value,
  5. Be sure to include the "Unique records only", you're done click on the OK button
  6. 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.
Advanced Filter in the service of a copy Unique values

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.
Copying unique items from column to another column

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 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.
Copying Unique values from multiple columns

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.
Copy distinct values from Column Cells in Excel

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.

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

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 = date 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 example of how to choose a formula for counting Unique Values.

Choose Count Unique formula in Excel

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