Ask Your Question

defining criteria for a duplicate then removing duplicate rows [closed]

asked 2014-01-15 22:44:22 +0100

rafael gravatar image

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?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-18 14:37:30.822654

1 Answer

Sort by » oldest newest most voted

answered 2014-01-16 21:00:44 +0100

Regina gravatar image
  • 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.
edit flag offensive delete link more

Question Tools

1 follower


Asked: 2014-01-15 22:44:22 +0100

Seen: 1,539 times

Last updated: Jan 16 '14