Mrinmoy Roy
In Excel, you can identify the occurrence of duplicate values using several methods. Among the duplicate values, their appearance in the data table can be of multiple times. In this tutorial, you will learn 4 different methods to count the order of occurrence of duplicates in Excel with ease.
Download Practice Workbook
You can download the Excel file from the following link and practice along with it.
4 Methods to Count the Order of Occurrence of Duplicates in Excel
1. Use COUNTIF Function to Count the Order of Occurrence of Duplicates in Excel
This method can count the order of occurrence of any type of values in Excel cells such as text, numbers, etc.
In this method, we will count the order of occurrence of the contents of the Category column.
Anyways, here are the steps to follow:
❶ First of all, insert the following formula in cell E5.
=COUNTIF($B$5:$B5, B5)Where,
- $B$5:$B5 is the cell range array.
- B5 is the first cell of the Category column.
❷ Now hit ENTER.
❸ Drag the Fill Handle icon from cell E5 to E12.
Finally, you will get the count of the order of occurrence of duplicates of the values stored in the Category column.
Related Content: Excel Count Number of Occurrences of Each Value in a Column
2. Join IF & COUNTIF Functions to Compute the Order of Occurrence of Duplicates in Excel
Now we will use a formula of the IF and COUNTIF function that does pretty much the same job as the previous method. But this time we will count the duplicates of the Price column instead of the Category column.
❶ Select cell E5 and insert the following formula:
=IF(COUNTIF(D:D,D5)>1,COUNTIF(D$5:D5,D5),"")Where,
- D:D cell range array.
- D5 is the very first cell of the Price column.
- COUNTIF(D:D,D5)>1 calculates whether each of the values of the Price column exists more than once.
- IF(COUNTIF(D:D,D5)>1,COUNTIF(D$5:D5,D5),””) returns the value of COUNTIF(D$5:D5,D5), if any duplicate value is found. Otherwise, it returns a null value.
❷ Now hit ENTER to finish inserting the formula.
❸ Now drag the Fill Handle icon to the end of the Order column.
After that, you will get the count of the order of the occurrence of duplicates of the Price column value in the Order column.
Read More: How to Count Duplicates in Column in Excel (3 Ways)
Similar Readings:
- Excel VBA to Count Duplicates in a Column (A Complete Analysis)
- How to Ignore Blanks and Count Duplicates in Excel (3 Ways)
- How to Count Duplicates Based on Multiple Criteria in Excel
- VBA to Count Duplicates in Range in Excel (4 Methods)
- How to Count Repeated Words in Excel (11 Methods)
3. Combine IF & COUNTIF Functions to Count the Running Order of Occurrence of Duplicates in Excel
This method also uses the IF and COUNTIF functions to build a formula that only returns the count of the order of occurrence of a certain specified value.
For example, we will only count the order of occurrence of the item “Wafer” from the Category column. Now follow the steps below to do so.
❶ Insert the following formula in cell E5.
=IF(B5="Wafer",COUNTIF($B$5:B5,"Wafer"),"")Where,
- B5=”Wafer” refers to the item “Wafer”.
- $B$5:B5 is the cell range array.
- “Wafer” is the sample item used for comparison to check duplicate values of it.
- IF(B5=”Wafer”,COUNTIF($B$5:B5,”Wafer”),””) returns the order of occurrence of “Wafer” using COUNTIF($B$5:B5,”Wafer”). If no duplicate is found it returns a null value.
❷ Now press the ENTER button to execute the formula.
❸ After that drag the Fill Handle icon from cell E5 to E12.
Finally, you will see the count of the order of occurrence of “Wafer” as in the picture below:
4. Use Only IF Function to Calculate the Order of Occurrence of Duplicates in Excel
If you want a very simple formula using the IF function to count the order of occurrence of duplicates then use this method.
All you need to do is,
❶ First insert 1 in the top cell of the Order count column. Which is the default count of the first item.
❷ After that insert the following formula in the next cell i.e. cell E6.
=IF(D6=D5,E5+1,1)Where,
- D6 is the second item of column Price from the top.
- D5 is the top cell of the column called Price.
- E5 is the cell containing the default count i.e. 1.
❸ Press ENTER button to finish entering the formula.
❹ To apply the same above formula all over the range in column Order, drag the Fill Handle icon to the end of the Order column.
In the end, you will get the order count of occurrence of duplicates of the items from the Price column as in the picture below:
Read More: How to Count Occurrences Per Day in Excel (4 Quick Ways)
Conclusion
To sum up, we have discussed 4 methods to count the order of occurrence of duplicates in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website Exceldemy to explore more.
