Blue Flower

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

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

 Advanced Filter in the service of a copy Unique Distinct values

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.

 Copying Unique Distinct items from column to another column

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.

 Copying Unique Distinct or Unique values from multiple columns

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.

 Copy Unique 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. (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.

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