Defining criteria for a duplicate then removing duplicate rows

[rafael]

There is a list of people who have registered for classes. Each class has a unique ID#.
There are some other columns such as “transaction date”, “receipt number”, “activity name”, etc. But I do not care about those.

Sometimes people will enroll into the same class multiple times. I do not want to include these rows. These are the duplicates I want to delete. That is, any row which has the same “name” and “class id #” value.

How do you do this?

[Regina]

• Add a column with numbers 1, 2, 3, … (enter 1, fill with double-click). This is later used to retain the original order.
• Add a column with label “IsIdentic”. This will later on contain the criteria.
• Define a data range for your data including the two additional columns.
• Sort the data range using the columns, which you will use for criteria, e.g.classID and Name. Now the records, which are identical in your criteria are consecutive.
• Add a formula, that checks, whether a record is identical to the previous in regard to your criteria. For example, when classId is in column B and Name is in column D, you can use =AND(B3=B2;D3=D2) in row 3. Fill column. In the first data row you set constant “=FALSE()”.
• Select the data range and filter it with criteria “column IsIdentic = FALSE”. Open the additional options part of the dialog and mark to copy the result, to an empty sheet for example.
• Sort the data range using the numbers entered in the first step to retain the original order.
• Delete the two helper columns.