Bug: Incorrect behavior while using Remove Duplicates in the Data Tab.

Question

While working with multiple sets of data and trying to remove duplicates using the option from Data menu, I observed a weird behaviour where the popup gives the count of the removed items greater than the count of entire data set on which Remove Duplicates was applied.

As seen in the below screenshot, the total count of values on which the Remove Duplicates was applied is: 448.

But the popup says it removed 1806 duplicates from it.

How can you remove 1806 duplicates from 448?

Regards,

Rahul.

Answer 1

Hi rahgupta,

The popup’s meaning may not be the same as what you understand.

In your case, the popup says that “1806 duplicate values found and removed; 449 unique values remain.” It means Excel has found 1806 duplicate values from all the cells you selected and removed them all. However, there’re 449 unique values in the selected cells, they were not removed and remained.

Best regards,

Madoc

Answer 2

Hi rahgupta,

The popup’s meaning may not be the same as what you understand.

In your case, the popup says that “1806 duplicate values found and removed; 449 unique values remain.” It means Excel has found 1806 duplicate values from all the cells you selected and removed them all. However, there’re 449 unique values in the selected cells, they were not removed and remained.

Best regards,

Madoc

Hi Madoc,

As evident from the status bar of the excel sheet in the screenshot, the number of cells selected is 448 only. How 1806 duplicates can be found in that and how 449 unique values can remain from 448?

Just to reiterate, I have only selected 448 cells from that Column I as seen in the screenshot and have applied 'Remove Duplicates' on that data only.

Regards,

Rahul.

Answer 3

Hi rahgupta，

I understand what you mean. I did a test but I cannot repro your issue from my side.

As the gif below shows(you can click on this image to see the image clearly), I selected 10 cells containing 4 duplicate values. After I clicked Data > Remove Duplicate, the duplicate values were removed correctly. And the number of the duplicated values removed and unique values remained were displayed properly as they should be.

So could you please share more details about the issue? This can help us better understand your situation and provide corresponding suggestions.

1. Which version of Office 365 are you using? You can click File > Account, and the Subscription Information is under Product Information, the Version Information contains in the About Excel box.
2. Prepare and upload a sample/ dummy file including the cells with no confidential/ sensitive data to OneDrive or other cloud drives and share the link here. In that case, I can further check your situation from my side.

Best regards,

Madoc