(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.
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:
which outputs the 2 distinct values in the data, “purple”, and “gray”.
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:
TRUE and FALSE can also be replaced with 1 and zero like this:
Excel formula for Beginners – How to find Distinct values in Excel
Free Machine Learning & Data Science Coding Tutorials in Python & R for Beginners. Subscribe @ Western Australian Center for Applied Machine Learning & Data Science.
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: