HOW TO FIND UNIQUE VALUES, UNIQUE TEXT VALUES OR UNIQUE NUMBERS IN EXCEL

Table of Contents

INTRODUCTION

Whenever we prepare any report in excel, we have two constituents in any report.

The Text portion and the Numerical portion.

But just storing the text and numbers doesn’t make the super reports. Many times we need to automate the process in the reports to minimize the effort and improve the accuracy.

Many functions are provided by the Excel which work on Text and give us the useful output as well. But few problems are still left on which we need to apply some tricks with the available tools.

THIS WAS AN EXCERPT FROM THE FIRST ARTICLE OF THIS SERIES MANIPULATING TEXT IN EXCEL – PART I

IN THIS ARTICLE, WE’LL LEARN TO COUNT THE UNIQUE TEXT VALUES IN EXCEL.

HOW TEXT IS HANDLED IN EXCEL?

TEXT is simply the group of characters and strings of characters that convey the information about the different data and numbers in Excel. Every character is connected with a code [ANSI].

Text comprises of the individual entity character which is the smallest bit that would be found in Excel. We can perform the operations on the strings[Text] or the characters.

Characters are not limited to A to Z or a to z but many symbols are also included in this which we would see in the latter part of the article. 

TEXT IS AN INACTIVE NUMBER TYPE[FORMAT] IN EXCEL. ANYTHING STORED AS TEXT [NUMBER OR DATE] WON’T RESPOND TO ANY STANDARD FORMULAS OR FUNCTIONS BUT SPECIALLY DESIGNED TEXT FUNCTIONS. [EXCEPTIONS DO OCCUR IN THE CASE OF NUMBERS]

If we need to make anything inactive, such as a Date to be nonresponding to the calculation, we put it as a text. Similarly, if we want to avoid any calculations for a number it needs to be put as a text.  

WHAT ARE UNIQUE VALUES?

Literally, Unique values are the ones that exist only once in the complete data but in general, and the default output of Unique function, unique values can also be taken as all the different values present in the data.

The correct word for finding all the available different types of values is DISTINCT VALUES.

LEARN THE WAY TO FIND AND COUNT DISTINCT VALUES IN EXCEL

For example, suppose we have a data as

1,2,3,3,4,5,6,6,5,4,7,8,9,0,9,0

if we want to find out the unique values as per Excel, it’ll give us the output as 1,2,3,4,5,6,7,8,9,0 i.e. 10 will be the count of the result of unique values as per excel but if we use the standard English language definition or exact once condition, only 1,2,7,8 are the four values which exist only once and the count will be 4.

If you want to find out the values which exist only once or twice and so on…. you can visit here.

But if you want to know all the distinct numbers which exist in the range, you can continue reading this article.

FIND AND COUNT UNIQUE TEXT VALUES IN EXCEL-SIMPLE SCENARIO

This can be a requirement sometimes when we need to count the unique text values in a given data or column. It can be done in a number of ways. Let us discuss a few.

SIMPLE SCENARIO: YOUR COLUMNS CONTAIN THE TEXT ONLY WITHOUT ANY BLANKS OR NUMBERS 
MIXED SCENARIO IS DISCUSSED IN THE NEXT SECTION

1. USING REMOVE DUPLICATES

2. USING UNIQUE FUNCTION

FIND AND COUNT UNIQUE VALUES USING REMOVE DUPLICATES:

This is an option already available in Excel directly and is very easy to use.

THIS OPTION IS NOT AUTOMATIC AND SHOULD BE USED ONLY IF WE DON’T HAVE MUCH DATA TO BE ANALYZED.

STEPS TO USE REMOVE DUPLICATES TO COUNT THE UNIQUE TEXT VALUES IN EXCEL

[*KINDLY VISIT HERE FOR COMPLETE INFORMATION ABOUT THE PROCEDURE OF REMOVING DUPLICATES IN EXCEL]

  • Select the complete data or column where we want to find the number of unique values. [ If we need the original column intact, copy the column and paste it somewhere else. We can perform operations on this column].
SAMPLE DATA
  • Go to HOME>DATA>REMOVE DUPLICATES.
  • Select the column from the dialog box. We have only one column so choose the column.
SELECT THE COLUMN
  • After selecting the column, press OK.
  • It’ll leave only unique values in the column.
  • Now , we can put the formula =COUNTA(FIRST CELL OF COLUMN: LAST CELL OF COLUMN). [HELP: COUNTA FUNCTION]
  • It’ll give us the number of unique text values present in the column.
STEPS TO COUNT THE TOTAL NUMBER OF UNIQUE TEXT IN EXCEL

FIND AND COUNT UNIQUE VALUES USING UNIQUE FUNCTION

In this method, we will make use of UNIQUE FUNCTION which will help us to find out the unique values or texts in the given column. After getting all the unique values we can count them easily. Let us start.

We’ll take an example for trying our procedure.

Suppose we have the following data.

SAMPLE DATA

STEPS TO COUNT UNIQUE TEXT VALUES IN A EXCEL USING UNIQUE FUNCTION.

[THE COMPLETE PICTORIAL REPRESENTATION OF THE USE OF UNIQUE FUNCTION IS HERE].

We have to process the information twice to get the desired results. First of all we need to find the unique values.

  • Select the cell where we want to find the unique values.
  • Put the following formula.
  • =UNIQUE(ARRAY,COLUMN-WISE, FALSE)
  • For our example, the formula will be
  • =UNIQUE(G6:G17, FALSE,0) [The first argument is the array containing the data. The second argument tell it that it has to do the comparison in the rows and the third tells that it can count the entries which are more than one].
  • The resulting array will contain only the unique values.
  • In the next column, we can count these by the use of COUNTA FUNCTION as =COUNTA(H6:H17). We can see in the picture that we had only 4 values but we took a wide range. The reason is that we don’t know what is the number of unique values if the data is too large.
  • Take a look at the picture below for reference.
STEPS TO COUNT THE TOTAL NUMBER OF UNIQUE TEXT IN EXCEL


MIXED SCENARIO

So, the cases we just discussed deal with the data where we have text only with or without any blanks. [ Because if there are any blanks, our intermediate step will remove those and keep only the single values. ] But what if we have a mix i.e. some numbers are also there in the same column.

MIXED SCENARIO: YOUR COLUMNS OR RANGES CONTAIN THE TEXT, NUMBERS AND BLANKS. 
Different cases are discussed below.



COUNT THE UNIQUE VALUES IN A COLUMN OR RANGE CONTAINING NUMBERS, TEXT OR BLANK CELLS IN EXCEL SHEET

Let us discuss the cases when there is a mix of numbers, text or blanks etc. too.

EXAMPLE:

Let us take an example of mixed data.

11
2
54
3
5
HELLO
2
WHAT
ARE
YOU
YOU
WHAT
3
5

Let us try to find out the way to count the unique values present in this data.

FOLLOW THE STEPS TO FIND THE UNIQUE VALUES IN THE GIVEN DATA.

  1. Enter the formula at the top cell where you want the result as =SUM(IFERROR(1/COUNTIF(H27:H41,H27:H41),0)) where H27:H41 is the range on which we apply this check. We can go for a complete column also.
  2. Press Enter.
  3. The result will appear.

COUNT UNIQUE VALUES IN EXCEL

The above procedure will find out the total number of different types i.e. unique values whether it is a number or text.



COUNT THE UNIQUE TEXT VALUES IN EXCEL IGNORING NUMBERS AND BLANKS

So, the cases we just discussed deal with the data where we have text only with or without any blanks. [ Because if there are any blanks, our intermediate step will remove those and keep only the single values. ] But what if we have a mix i.e. some numbers are also there in the same column. [ Although it happens quite less because a column is dedicated for a particular value only. It can be a text or number only.]

But even then, we’ll discuss the way to tackle the same.

EXAMPLE:

Let us take an example of mixed data.

11
2
54
3
5
HELLO
2
WHAT
ARE
YOU
YOU
WHAT
3
5

Let us try to find out the way to count the unique values present in this data.

FOLLOW THE STEPS TO FIND THE UNIQUE VALUES IN THE GIVEN DATA.

  1. Enter the formula at the top cell where you want the result as =SUM(ISTEXT(H4:H18)*IFERROR(1/COUNTIF(H4:H18,H4:H18),0)) where H4:H18 is the range on which we apply this check. We can go for a complete column also.
  2. Press Enter.
  3. The result will appear.

COUNT UNIQUE TEXT VALUES IN EXCEL

The above procedure will find out the total number of different types of TEXT VALUES available ignoring all the numbers or blanks.



COUNT THE UNIQUE NUMBER VALUES IN EXCEL IGNORING TEXT VALUES AND BLANKS

Just like the previous case, where we discussed counting the unique text values only ignoring the blanks and numbers , let us now try to count the unique number values only ignoring the text and blanks.

But even then, we’ll discuss the way to tackle the same.

EXAMPLE:

Let us take an example of mixed data.

11
2
54
3
5
HELLO
2
WHAT
ARE
YOU
YOU
WHAT
3
5

Let us try to find out the way to count the unique values present in this data.

FOLLOW THE STEPS TO FIND THE UNIQUE VALUES IN THE GIVEN DATA.

  1. Enter the formula at the top cell where you want the result as =SUM(ISNUMBER(H27:H41)*IFERROR(1/COUNTIF(H27:H41,H27:H41),0)) where H27:H41 is the range on which we apply this check. We can go for a complete column also.
  2. Press Enter.
  3. The result will appear.

COUNT UNIQUE NUMBER VALUES IN EXCEL

The above procedure will find out the total number of different types i.e. unique NUMBER TYPE VALUES available ignoring all the text or blanks.