Removing Everything Except Duplicates

Barry knows he can use the Remove Duplicates tool on the Data tab of the ribbon to remove duplicate rows. He wonders, though, if there is a way to do the opposite, to remove any rows that are not duplicated and leave only the duplicate rows.

There isn't any built-in Excel tool or function that will give the desired results, but there is an easy way to accomplish the task using a helper column. For the sake of simplicity, let's say that you determine whether a row is a duplicate or not based entirely on the contents of column A. In an unused cell, you could add the following formula to row 2 of whatever column you want to use as the helper:

=COUNTIF(A:A,A2)

The formula returns a count of how many times the value in A2 occurs in column A. If the value returned is 1, then you know the row is not a duplicate; it is unique. Copy the formula down for as many rows as necessary, then you can filter based on the helper column. You can filter to show only the rows that have 1s and delete them. Or, you could filter to show only rows that are greater than 1. These are the duplicates, and you could copy them to a different location.

If you need more than a single row to determine what is a duplicate or not, then you can use two helper columns. Let's say that the determining columns are A, B, C, and D. Further, let's assume that you are using column H as your first helper column. In that case, you would enter the following into cell H2:

=A2&B2&C2&D2

This concatenates the first four cells in the row. If you are using Excel 2019, Excel 2021, or the version of Excel in Microsoft 365, you could use the following formula, instead:

=CONCAT(A2:D2)

Copy this down as many rows as needed, and then enter this into cell H2:

=COUNTIF(H:H,H2)

Once you copy this down for all the rows, you again end up with an indication of how many rows are duplicated, if the result is greater than 1. And, again, you can delete filtered rows or copy filtered rows, as necessary.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13945) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.