Highlight & Delete Duplicate Rows

If you are working with large data size with numerous data fields, there could be duplicate records in data which needs to highlighted and corrected to make data more meaningful and usable.

In example below (See picture 1), we have ROW number 2 and 7 which are duplicate and needs to be highlighted.

This can done using 2 simple formulas - CONCATENATE and COUNTIF

  1. First collate all records using CONCATNATE function, In cell F2, create function as explained below-

          = CONCATENATE (A2,B2,C2,D2,E2)

                and copy same formula from F2 till F7. See picture 2

  1. Secondly, use COUNTIF function to highlight duplicate records. Basic use of COUNTIF function is to give a Count of records which are repeating in database. In cell G2, type COUNTIF function as explained below-

         =COUNTIF (E$2:E2,E2)

 In above function, a absolute reference is given to cell E2 by putting a dollar($) sign in front of number 2 to fix the starting cell. When the above function is copied to cells below, cell reference with $ sign is fixed while other references are adjusted to refer cells E3, E4 and so on.

In this way, the function will only count cells from starting cell E2 to highlighted cell which are repeating in column F. See picture 2

In this way, COUNTIF Function will display result as 1 in all cells from G2 to G6 and 2 in cell G7 as this record has appeared once above.

Finally use Conditional Formatting to highlight rows where values in column G are Greater than 1. See picture 3.

To further analyze specificduplicate records and make required corrections, we can use filters on concatenated field(column F).