It is quite easy for you to get unique value where there are so many duplicates, especially if you would like to paste them in a new location. We are going to count unique values together.

Have a data with duplicates.

Select the range of cells, or have it all in a table.

Advanced Filter

Click on Data Tab (1), and choose Advanced (2) in Sort & Filter.

Click yes

Click copy to another location (1), where you want to copy it to (2), check on the unique records only (3), and click ok.

List Range: =&A&1:&A&14

Note: If the cells you choose in number 2 isn’t large enough to contain all the unique numbers, Excel would automatically enlarge it.

Unique Values:

Sumproduct Formula

Alternative solution would be to use Sumproduct function combined with Countif function.

The formula to copy paste is: =SUMPRODUCT(1/COUNTIF(A2:A14,A2:A14)) where A2:A14 is the range of data set.

Sum Formula

Another method to count unique values is a sum function combined with countif function again.

The formula is as follows: {=SUM(1/COUNTIF(A2:A14,A2:A14))}

For sure you have noticed curly braces. The formula is an array formula so to enter that you need to use CTRL + SHIFT + Enter buttons.

As you can see there is a possible that data in your tables or reports will be duplicated. To extract only disctinct/unique data like products/employee/companies/cities etc. just use this trick.

You can also count distinct values in pivot table.

How to count unique values using Vba?

To count the number of unique values in a range using VBA in Microsoft Excel, you can use the following code:

Function CountUnique(rng As Range) As Long

  Dim dict As Object

  Set dict = CreateObject("Scripting.Dictionary")

  For Each cell In rng

    dict(cell.Value) = 1

  Next

  CountUnique = dict.Count

End Function 

To use this code, you need to follow these steps:

1. Open the Excel file that contains the data you want to count unique values for.
2. Press ALT + F11 to open the VBA editor.
3. In the editor, right-click on the workbook name in the Project Explorer and choose “Insert” and then “Module.”
4. Paste the code into the new module.
5. Press ALT + F11 to return to the Excel worksheet.
6. In a cell, enter the formula =CountUnique(A1:A10), replacing the range A1:A10 with the range of cells you want to count unique values for.
7. Press Enter to get the result.

This code uses a Scripting.Dictionary object to store the unique values and their frequency. The Count property of the dict object returns the number of unique values.

Related posts:

How to Count Repeated Words in Excel How to Find Duplicates in Excel How to Get Data from another Sheet?