Hello everyone! Did you know……Microsoft Excel can easily find duplicate rows by using the COUNTIF function.
book a session
Join up row’s cells’ content as first step to find duplicate rows
First of all, on a blank column to the right of our data, for each cell in this new column we join up all the cells on the same row to the left using the “&” symbol, e.g.
=A6&B6&C6
What a “full” duplicate row is
So any cell in this new column, in say column D, which has exactly the same value as another cell in this column is definitely a full duplicate row.
Let’s now deploy the COUNTIF function
Beside this first new column we now set up another new column, say in column E, with the COUNTIF function, i.e.
= COUNTIF(first new column range,new cell on the current row in first new column)
or as in an example:
=COUNTIF($D$6:$D$100,D6)
The non-duplicates
Those cells which result in a count of “1” indicate a non-duplicate, as shows appearing only one time in this new column.
Duplicate rows found
Any cell which counts to greater than “1” is obviously a duplicate.
Filter it
Apply a filter and you can filter out the duplicate rows by filtering in the “1”s only.
Additional Info
Bring back in a duplicate
You can filter in the duplicates (Greater Than 1) and delete any unwanted rows.
The Conditional Formatting alternative
Instead of using the COUNTIF column we could instead apply conditional formatting to the JoinedUp column. So highlight this column and click through the following command path:
Home / Styles / Conditional Formatting / Highlight Cells Rules / Duplicate Values
Format cells that contain – select “Duplicate”
values with – select a suitable colour format
Ignoring the first duplicate
We could actually identify in our COUNTIF column the first duplicate as a non-duplicate. This would be the adjusted formula to enter in cell E6 (and then copy down in column E):
=COUNTIF($D$6:$D6,D6)
This is probably the best approach?