Prantick Bala
Sometimes our Excel dataset may have duplicate values. These duplicates can be just multiple entries of the same data. To find out how many duplicates our dataset has and to take further actions such as removing the duplicates we may need to group the duplicates together. In this article, I’ll demonstrate 3 easy and effective methods to group duplicates in Excel.
Consider the following dataset with several duplicate values. Now, I’ll show you how you can group duplicates using this dataset.
Download Practice Workbook
3 Methods to Group Duplicates in Excel
1. SUMPRODUCT Function to Group Duplicates
With the SUMPRODUCT function you can find the number of duplicates of a dataset and you can use this number to group the duplicates together. First,
➤ Type the following formula in the first cell of the first empty column next to your dataset (E5),
=SUMPRODUCT(--(B5&C5&D5=$B$5:$B$15&$C$5:$C$15&$D$5:$D$15))Here the formula will return 1 for each unique data and the number of occurrences for data with duplicate values.
➤ Press ENTER
As a result, you will get the number of duplicates for the first data. 1 means the data has been found one time in the whole dataset.
Now,
➤ Drag cell E5 to the end of your dataset.
As a result, we will get the number of duplicates for all data.
After that,
➤ Right click on the selected cells.
A dropdown menu will appear. From this menu,
➤ Click on Sort to Expand it and select Sort Largest to Smallest.
It will rearrange the rows of your dataset and will put the duplicates together. So, the duplicates will be grouped together.
Now let’s see how you can extract the unique values from this dataset. First,
➤ Select your entire dataset.
➤ Go to Data > Sort & Filter > Advanced.
As a result, a box named Advanced Filter will appear.
➤ Select the action Copy to another location, select an empty cell in the Copy to box, check the Unique records only box, and click on OK.
As a result, you will see, only the unique values are copied into the new location.
2. Group Duplicates with Conditional Formatting
You can also group duplicates with conditional formatting. But before applying conditional formatting we need to make a column of combined data including all of the strings from previous columns of the same row.
➤ Type the following formula in cell E5,
=B5&C5&D5The formula will combine cells B5, C5, and D5 and will return in cell E5.
➤ Press ENTER
As a result, you will get the combination of cells B5, C5, and D5 in cell E5.
Now,
➤ Drag cell E5 to the end of your dataset.
As a result, you will get the combined data for all other cells.
At this time,
➤ Select your dataset and go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
A window named Duplicate Values will be opened.
➤ Select a formatting style in the values with box. (For this dataset I’ve chosen Yellow Fill with Dark Yellow Text).
After that,
➤ Press OK.
At this step,
➤ Go to Home > Editing > Sort & Filter > Filter.
As a result, a little downward arrow will appear beside each column header.
➤ Click on the arrow besides Combined.
A filtering menu will appear,
➤ Go to Filter by Color and select the formatting cell color (Yellow) from Filter by Cell Color. Then click on OK.
Now, you will see all the duplicates of your dataset are grouped together.
3. Group Duplicates by Combining COUNTIFS and IF Functions
You can also group Duplicates by using the COUNTIFS function and the IF function altogether. First,
➤ Type the following formula in cell E5,
=IF(COUNTIFS($B$5:$B$15,B5,$C$5:$C$15,C5,$D$5:$D$15,D5)=1,"Unique","Duplicates")The formula will return Unique if the data of row 5 has no other copy and Duplicates if the data of row 5 matches with the data of any other rows.
➤ Press ENTER.
Cell E5 will show Unique that means the data of row 5 is unique.
Now,
➤ Drag cell E5 to the end of your dataset.
After that,,
➤ Go to Home > Editing > Sort & Filter > Filter.
As a result, a little downward arrow will appear besides the Column headers.
➤ Click on the downward arrow of the column header Status.
A filtering menu will appear.
➤ Check on Duplicates and click on OK.
As a result, you will get the duplicates grouped together.
To get the same values altogether,
➤ Click on the arrow besides Salesman column (You can also produce the same result by selecting any other columns except the Status column)
It will open the filtering menu.
➤ Check on only one name from the list and click on OK.
As a result you will get the same values grouped together.
Conclusion
By following any of the above described methods you will be able to group duplicates in Excel. If you have any kind of confusion, please leave a comment.
