Sakibul Hasan Nahid
Sometimes our Excel database may include duplicate values. These repetitions may be several instances of identical data. To determine the number of duplicates in our dataset and perform actions such as deleting duplicates, we might need to categorize duplicates together. In this post, I will present three simple and efficient ways to group duplicates in excel.
📁 Download Excel File
Download this Excel file below.
Learn to Group Duplicates in Excel These 3 Suitable Approaches
Here in this article, we will group duplicates in excel using three easy ways. We will use the following dataset for this purpose.
1. Using SUMPRODUCT Function
In the first method, we will use the SUMPRODUCT Function to group together the excel duplicate data. We are going to follow the given steps below:
⬇️⬇️ STEPS ⬇️⬇️
- First, go to the SUMPRODUCT worksheet.
- Select cell E6.
- Enter the following formula.
- Press the Enter key.
- Select cell E6 and drag the Fill Handle icon from E6 to E16.
- Then we will get the number of times the same values were repeated. Then select cells E6 to E16 and right-click the mouse. You will get the following options. Select Sort from the options.
- From the Sort, menu select Sort Largest to Smalllest.
- Then select Expand the section and click Sort.
- Then we will see that the values are sorted following the number of duplicates And the repeated values have been grouped.
Now if we want to extract the unique values from this dataset we can use the following process.
- Select the data range. Go to the Data Tab and select the Advanced option.
- In Advance Filter select Copy to another location. In the Copy to option select the cell where you want to extract the values. Click on Unique records only and Press OK.
- Then we will see the following values have been extracted to the new location.
2. Applying Conditional Formatting
We can also group duplicates using the Conditional Formatting option. But before applying the conditional formatting, we will combine the columns of the same row.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly we will go to the applying conditional formatting worksheet. And select Cell E6.
- Then we will enter the following formula to combine all the previous columns of row 6.
- Click the Enter key.
- Select cell E6 and drag the Fill Handle icon from E6 to E16.
- Then go to the Conditional Formatting option in the excel toolbar and click on Duplicate Values.
- After clicking Duplicate Values you will get the following window. Then Select Duplicate and Light Red Fill with Dark Red Text. Then Click OK.
- We will get the following result. Where all the duplicate cells will become light red.
- Then select the dataset and go to Sort and Filter option in the toolbar and click on Filter.
- Then the dataset will look like the below.
- Then click on the down arrow of the Combination column and Select filter by color and the click on Filter by cell color.
- Then we will only see the duplicate values.
This is how we can group the duplicate values in excel using Conditional Formatting.
3. Using Combination of IF and COUNTIF Functions
In the third method, we are going to use a combination of IF and COUNTIF functions to group the duplicate values.
⬇️⬇️ STEPS ⬇️⬇️
- First, go to the IF worksheet and select cell E6.
- Enter the following formula.
- Then click Enter.
- Select cell E6 and drag the Fill Handle icon from E6 to E16.
- This will give us the status, of whether the value is duplicate or not. Select the data range and go to the Home Tab. From the toolbar in the Editing option select Sort and Filter and then click on Filter.
- Then the table will look like the one below. Then click on the down arrow of the Status column and select Duplicates. After that click OK.
- And we will see only the duplicate values are present. So the duplicate values are grouped together here.
How to Merge Duplicates in Excel
Sometimes we need to merge the duplicate values in excel. Here we will learn how to merge duplicate values together. Here we will use the following dataset below. This dataset is a little bit different from the previous dataset we used to group the duplicate values.
⬇️⬇️ STEPS ⬇️⬇️
- Select the table. Go to the home tab, from the Editing tool select Sort and Filter, and then click on Custom Sort.
- Then we will get the following window. Here select Student ID as the sort criteria to sort the data. Then click OK.
- Then we will see that the same Student IDs are grouped together.
- Then select the same student ids and click on Merge and Center.
- Then the table will look like this.
- We will do the same for every duplicate value in the Student ID and Student Name column following the previous step. And Finally our table will look like this.
Here we merged all the duplicate student ids and names together.
📝 Takeaways from This Article
📌 In this article we learned how to group duplicate values.
📌 We have learned the use of the Sort and Filter option.
📌 We also used some functions. For example IF, COUNTIF, and SUMPRODUCT.
📌 Finally at the end of the article we also learned a way to merge the duplicate values.
Conclusion
In this post, we tried to show every method to group duplicates in excel. So that you may choose the appropriate approach for you. Please post any queries in the comments section below. Finally, we recommend visiting Excelden to learn more about Excel.
