Excel formula for Beginners – How to find Distinct values in Excel

Hits: 18

 

 

(Excel examples for Beginners)

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

 

Excel formula for Beginners – How to find Distinct values in Excel

Generic formula

=UNIQUE(data,FALSE,TRUE)

Summary

To extract a list of distinct values from a set of data (i.e. values that appear just once), you can use the UNIQUE function. In the example shown, the formula in D5 is:

=UNIQUE(B5:B16,FALSE,TRUE)

which outputs the 2 distinct values in the data, “purple”, and “gray”.

Explanation

This example uses the UNIQUE function. With default settings, UNIQUE will output a list of unique values, i.e. values that appear one or more times in the source data. However, UNIQUE has an optional third argument, called “occurs_once” that, when set to TRUE, will cause UNIQUE to return only values that appear once in the data.

In the example shown, UNIQUE’s arguments are configures like this:

  • array – B5:B16
  • by_col – FALSE
  • occurs_once – TRUE

Because occurs_once is set to TRUE, UNIQUE outputs the 2 values in the data that appear just once: “purple”, and “gray”.

Notice the by_col argument is optional and defaults to FALSE, so it can be omitted:

=UNIQUE(data,,TRUE)

TRUE and FALSE can also be replaced with 1 and zero like this:

=UNIQUE(data,0,1)

Excel formula for Beginners – How to find Distinct values in Excel

 

 

Sign up to get end-to-end “Learn By Coding” example.


 

 

Introduction to Applied Machine Learning & Data Science for Beginners, Business Analysts, Students, Researchers and Freelancers with Python & R Codes @ Western Australian Center for Applied Machine Learning & Data Science (WACAMLDS) !!!

Latest end-to-end Learn by Coding Projects (Jupyter Notebooks) in Python and R:

Applied Statistics with R for Beginners and Business Professionals

Data Science and Machine Learning Projects in Python: Tabular Data Analytics

Data Science and Machine Learning Projects in R: Tabular Data Analytics

Python Machine Learning & Data Science Recipes: Learn by Coding

R Machine Learning & Data Science Recipes: Learn by Coding

 

There are 2000+ End-to-End Python & R Notebooks are available to build Professional Portfolio as a Data Scientist and/or Machine Learning Specialist. All Notebooks are only $19.95. We would like to request you to have a look at the website for FREE the end-to-end notebooks, and then decide whether you would like to purchase or not.

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!