3 Ways to Group Duplicates in Excel

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.

group duplicates in excel dataset


APPROACH

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.

Using SUMPRODUCT function to group duplicates in excel.

  • Select cell E6.
  • Enter the following formula.
=SUMPRODUCT(–(B6&C6&D6=$B$6:$B$16&$C$6:$C$16&$D$6:$D$16))

implementing the formula

  • Press the Enter key.

Utilizing SUMPRODUCT function to group duplicates in excel.

  • Select cell E6 and drag the Fill Handle icon from E6 to E16.

result obtained using sumproduct function.

  • 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.

Applying SUMPRODUCT function to group duplicates in excel.

  • From the Sort, menu select Sort Largest to Smalllest.

sorting the obtained result

  • Then select Expand the section and click Sort.

sorting based on large value to small values

  • Then we will see that the values are sorted following the number of duplicates And the repeated values have been grouped.

sorted values

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.

advanced sorting

  • 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.

copy unique values to another location

  • Then we will see the following values have been extracted to the new location.

extracted unique values


APPROACH

 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.

Using Conditional formatting function to group duplicates in excel.

  • Then we will enter the following formula to combine all the previous columns of row 6.
 =B6&C6&D6

merging columns

  • Click the Enter key.

Applying Conditional Formatting to group duplicates

  • Select cell E6 and drag the Fill Handle icon from E6 to E16.

formatting based on merged values

  • Then go to the Conditional Formatting option in the excel toolbar and click on Duplicate Values.

Utilizing Conditional Formatting to group duplicates

  • After clicking Duplicate Values you will get the following window. Then Select Duplicate and Light Red Fill with Dark Red Text. Then Click OK.

highlighting Duplicate values

  • We will get the following result. Where all the duplicate cells will become light red.

utilizing Conditional Formatting to group duplicates

  • Then select the dataset and go to Sort and Filter option in the toolbar and click on Filter.

 Conditional Formatting to group duplicates

  • Then the dataset will look like the below.

Implementing Conditional Formatting

  • Then click on the down arrow of the Combination column and Select filter by color and the click on Filter by cell color.

filtering duplicate values

  • Then we will only see the duplicate values.

grouping duplicate values

This is how we can group the duplicate values in excel using Conditional Formatting.


APPROACH

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.

Dataset for IF function

  • Enter the following formula.
=IF(COUNTIFS($B$6:$B$16,B6,$C$6:$C$16,C6,$D$6:$D$16,D6)=1,”Distinct”, “Duplicates”)

Applying IF and COUNTIF to group duplicates

  • Then click Enter.

obtained results

  • Select cell E6 and drag the Fill Handle icon from E6 to E16.

identifying duplicates and distinct values

  • 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.

Filter the result to get the final output

  • 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.

filtering duplicate values

  • And we will see only the duplicate values are present. So the duplicate values are grouped together here.

grouped duplicate values


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.

dataset for merge duplicates in excel.

⬇️⬇️ STEPS ⬇️⬇️

  • Select the table. Go to the home tab, from the Editing tool select Sort and Filter, and then click on Custom Sort.

custom sorting

  • Then we will get the following window. Here select Student ID as the sort criteria to sort the data. Then click OK.

sorting based on student id

  • Then we will see that the same Student IDs are grouped together.

sorted values

  • Then select the same student ids and click on Merge and Center.

merging same values

  • Then the table will look like this.

merged rows

  • 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.

merged student name

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.

(Visited 43 times, 1 visits today)
Sakibul Hasan Nahid

Sakibul Hasan Nahid

Hello I am Sakibul Hasan Nahid. I have completed my BSc. in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. I take a great interest in learning about new technologies and sharing my ideas and thoughts on them with others. Please do reach out to me if you have any queries or recommendations.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo