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

# 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