Excel formula for Beginners – How to find Unique values by count in Excel

Hits: 20

(Excel examples for Beginners)

In this end-to-end excel example, you will learn – Excel formula for Beginners – How to find Unique values by count in Excel.

 

Excel formula for Beginners – How to find Unique values by count in Excel

Generic formula

=UNIQUE(FILTER(data,COUNTIF(data,data)>n))

Summary

To extract a list of unique values from a set of data, filtered by count or occurence, you can use UNIQUE with FILTER, and apply criteria with the COUNTIF function. In the example shown, the formula in D5 is:

=UNIQUE(FILTER(data,COUNTIF(data,data)>1))

which outputs the 3 unique values that appear more than once in the named range “data” (B5:B16).

Note: In this example, we are extracting a unique list of values that appear more than once. In other words, we are creating a list of duplicates 🙂 The language is somewhat confusing.

Explanation

This example uses the UNIQUE function together with the FILTER function. You can see a more basic example here.

The trick in this case is to apply criteria to the FILTER function to only allow values based on count of occurence. Working from the inside out, this is done with COUNTIF and the FILTER function here:

FILTER(data,COUNTIF(data,data)>1)

The result from COUNTIF is an array of counts like this:

{3;1;3;3;2;1;1;3;1;2;3;3}

which are checked with the logical comparison > 1 to yield an array or TRUE/FALSE values:

{TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;TRUE;TRUE}

Notice TRUE corresponds to values in the data that appear more than once. This array is returned to FILTER as the include argument, used to filter the data. FILTER returns another array as a result:

{"red";"green";"green";"blue";"red";"blue";"red";"green"}

This array is returned directly to the UNIQUE function as the array argument. Notice of the 12 original values, only 8 survive.

UNIQUE then removes duplicates, and returns the final array:

{"red";"green";"blue"}

If values in B5:B16 cchange, the output will update immediately.

Count > 2

The formula in F5, which lists colors appearing at least 2 times in the source data, is:

=UNIQUE(FILTER(data,COUNTIF(data,data)>2))

Dynamic source range

Because data (B5:B15) is a normal named range, it won’t resize if data is added or deleted. To use a dynamic range that will automatically resize when needed, you can use an Excel Table, or create a dynamic named range with a formula.

 

Personal Career & Learning Guide for Data Analyst, Data Engineer and Data Scientist

Applied Machine Learning & Data Science Projects and Coding Recipes for Beginners

A list of FREE programming examples together with eTutorials & eBooks @ SETScholars

95% Discount on “Projects & Recipes, tutorials, ebooks”

Projects and Coding Recipes, eTutorials and eBooks: The best All-in-One resources for Data Analyst, Data Scientist, Machine Learning Engineer and Software Developer

Topics included: Classification, Clustering, Regression, Forecasting, Algorithms, Data Structures, Data Analytics & Data Science, Deep Learning, Machine Learning, Programming Languages and Software Tools & Packages.
(Discount is valid for limited time only)

Disclaimer: The information and code presented within this recipe/tutorial is only for educational and coaching purposes for beginners and developers. Anyone can practice and apply the recipe/tutorial presented here, but the reader is taking full responsibility for his/her actions. The author (content curator) of this recipe (code / program) has made every effort to ensure the accuracy of the information was correct at time of publication. The author (content curator) does not assume and hereby disclaims any liability to any party for any loss, damage, or disruption caused by errors or omissions, whether such errors or omissions result from accident, negligence, or any other cause. The information presented here could also be found in public knowledge domains.

Learn by Coding: v-Tutorials on Applied Machine Learning and Data Science for Beginners

Please do not waste your valuable time by watching videos, rather use end-to-end (Python and R) recipes from Professional Data Scientists to practice coding, and land the most demandable jobs in the fields of Predictive analytics & AI (Machine Learning and Data Science).

The objective is to guide the developers & analysts to “Learn how to Code” for Applied AI using end-to-end coding solutions, and unlock the world of opportunities!