It often happens that your data may contain duplicates that could be a real-time mess because they can get in the way of calculating multiple values from the data. Today, you will learn how to group duplicates in Excel and for this, you need to find how many duplicates are there in the dataset. Sometimes, you need to remove those duplicates and sometimes you need to group them.
Let’s see how you can do it:
SUMPRODUCT Function to Group Duplicates:
To find how many duplicates are present in the data, you can use the SUMPRODUCT function. Later on, this number can be used to group the duplicates altogether.
Let’s follow the steps given below:
- Enter the following formula in the first cell of the first blank column that appears beside your data. Here E5 is the first cell:
=SUMPRODUCT(–(B5&C5&D5=$B$5:$B$15&$C$5:$C$15&$D$5:$D$15))
- You will get 1 in return for each unique data and the number of duplicates that appear in the data.
- Press ENTER key.
- Now, you will get the duplicate values in front of you. Here 1 shows that the data has been found one time in the entire set.
- Now, drag cell E5 till the end.
- You will find all the duplicates present in the data.
- Now, right-click on the selected cells, and you will see a drop-down menu.
- From there, select the Sort option and then choose Sort Largest to Smallest.
- You will notice all the rows are arranged once again and the duplicates are merged together. Ultimately, the duplicates are grouped together.
- Now, suppose you need to drag all the unique values present in the dataset. For this,
- Choose the whole dataset.
- Open the Data tab, click on the Sort & Filter option, and then click on Advanced.
- You will notice the Advanced Filter box pops up.
- From there, choose the action Copy to another location and then choose a blank cell in the Copy to the box.
- Click OK once you checked the Unique records only box.
- You will simply notice the unique values are copied into the new location.
How to Group Duplicates in Excel with COUNTIFS and IF Functions?
With the help of the COUNTIFS function and IF function mutually, you can easily group duplicate values available in a dataset. For this, you can follow the steps given below:
- Enter 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”)
- If you see that row 5 does not have any duplicates, the formula will then give you Unique in return. On the other hand, if row 5 matches with the data of other rows, you will get the Duplicates in return.
- Press ENTER key.
- You will notice E5 cell appears with Unique, which simply means row 5 is unique.
- Now, you need to drag cell E5 till the end.
- Open the Home tab and choose the Editing option.
- Click on the Sort & Filter menu and choose Filter.
- Besides the Column headers, you will notice a small downward arrow.
- Click on this arrow and a filtering menu will pop up.
- Click OK once you checked the Duplicates box.
Ultimately, all the duplicates are grouped together.
You can even combine all the values at the same time by following the steps given below:
- Click on the arrow that appears beside the Salesman column.
- A filtering menu will pop up.
- Now, find one name only from the list.
- Click OK.
Eventually, you will find similar values grouped at once. That’s it!
How to Group Duplicates in Excel with Conditional Formatting?
Conditional formatting is also useful to group duplicate values in Excel. For this procedure, you need to make a column of merged data containing all the strings from the previous column of the same row.
Let’s follow the steps:
- Enter the following formula in cell E5:
=B5&C5&D5
- You will find the formula in cell E5 as it combines all the cells B5, C5, and D5.
- Press ENTER key.
- You will notice a combo of cells B5, C5, and D5 in cell E5.
- Now, you need to drag E5 till the end. And merged data for all other cells will be there.
- Choose the dataset and open the Home tab.
- From the menu, click on the Conditional Formatting and then choose the Highlight Cells Rules option.
- Now, click on the Duplicate Values option.
- Duplicate Values window will pop up.
- From the Values with box, choose a formatting style.
- Press OK.
- Now, once again open the Home tab.
- Choose the Editing menu, click Sort & Filter option, and then choose Filter.
- Now, you may notice a small downward arrow appears beside all the column headers.
- Click on this small arrow.
- You will see a filtering menu appears. Open the Filter by Color option and choose the formatting cell color from Filter by Cell Color.
- Click OK.
Now, all the duplicate values from the dataset are grouped together.
Summary:
In this post, you have learned how to group duplicates in Excel by using multiple methods. Each method is worth enough to try. Excel lets you perform all kinds of operations with ease. Just understand functions and apply them carefully.
