How to Group Duplicates in Excel (3 Easy Techniques)

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.

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.

the SUMPRODUCT function

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

output

Now,

➤ Drag cell E5 to the end of your dataset.

As a result, we will get the number of duplicates for all data.

all output

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.

group duplicates in Excel

It will rearrange the rows of your dataset and will put the duplicates together. So, the duplicates will be grouped together.

group duplicates in Excel

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.

group duplicates in Excel

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.

advanced filter

As a result, you will see, only the unique values are copied into the new location.

group duplicates in Excel


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&D5

The formula will combine cells B5, C5, and D5 and will return in cell E5.

formula

➤ Press ENTER

As a result, you will get the combination of cells B5, C5, and D5 in cell E5.

combined

Now,

➤ Drag cell E5 to the end of your dataset.

As a result, you will get the combined data for all other cells.

all combined

At this time,

➤ Select your dataset and go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.

conditional formatting

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.

duplicate values

At this step,

➤ Go to Home > Editing > Sort & Filter > 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.

group duplicates in Excel

Now, you will see all the duplicates of your dataset are grouped together.

group duplicates in Excel


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.

the COUNTIF function

➤ Press ENTER.

Cell E5 will show Unique that means the data of row 5 is unique.

output

Now,

➤ Drag cell E5 to the end of your dataset.

all output

After that,,

➤ Go to Home > Editing > Sort & Filter > Filter.

group duplicates in Excel

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.

all filter

As a result, you will get the duplicates grouped together.

group duplicates in Excel

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.

same values

As a result you will get the same values grouped together.

group duplicates in Excel


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.


Further Readings

Prantick

Prantick

Hi, I'm Prantick Bala, currently working and doing research on Microsoft Excel in Softeko.com. I have completed BSc in Naval Architecture and Marine Engineering From Bangladesh University of Engineering and Technology (BUET). Currently I am in the last semester of my MBA at Institute of Business Administration (IBA), Dhaka University (DU). I'm always eager to learn through experience and have a deep interest in solving problem to get optimum solution.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo