Finding Duplicate Records

Why Duplicate Records Need Careful Review

A duplicate record is a row that appears to repeat information already represented in the data. It may be an accidental second entry, an import problem, or a legitimate additional observation. Those possibilities have different consequences: removing a genuine repeat can discard information, while keeping an accidental copy can give one event extra weight in later summaries.

A spreadsheet can flag rows that match, but it cannot determine by itself whether they are errors. The meaning of a row depends on what one row represents and which fields distinguish one record from another. A useful check therefore has two parts: identify candidate matches systematically, then review them using the data's meaning and available context.

For example, if one row represents one appointment, an appointment identifier may distinguish records. If the data have no identifier, a combination such as patient code, appointment date, and appointment time might be used as a provisional key. That combination is only useful if it fits the way the records were collected. Two observations about the same person are not automatically duplicates.

Choose What to Compare

Before sorting or applying a spreadsheet check, state what a row represents and what fields should identify one record. This prevents two common errors: treating similar but distinct observations as duplicates, and overlooking duplicate entries because an irrelevant field differs.

• Check all fields when looking for exact copies of entire rows. An exact match is a useful warning, but it still may represent two genuine events recorded identically.
• Check an identifier when a field is intended to be unique for each record. Repeated identifiers deserve investigation, but may be valid if the identifier actually refers to a person or object with multiple observations.
• Check a combination of fields when no single field identifies a record. The chosen fields should distinguish the events the data are meant to represent.

A field combination is sometimes called a key. A key is not simply a convenient set of columns: it asserts that records matching on those columns refer to the same underlying record or event. If that assertion is uncertain, describe the check as a candidate match and inspect the rows rather than treating the key as definitive.

Missing values also affect interpretation. Two rows that are blank in the same key field may match in a spreadsheet check, but blank does not establish that the underlying values were the same. Use the recorded missing-value conventions and available source information when evaluating a match; do not infer identity from shared blanks.

Finding Candidates in a Spreadsheet

Sorting is a simple first check. Sort by the suspected identifier or by the fields in the proposed key. Matching values will appear together, making repeated values easier to inspect. Sorting changes the order of rows, not their content, and it does not decide which rows are duplicates. Keep the header row intact and make sure all columns are included in the sort so that values from different records are not separated.

A counting formula can flag repeated values. If identifiers are in cells A2 through A7, a spreadsheet can count how many times each row's identifier appears in that range. The formula below, entered beside row 2 and filled down, returns the frequency of the value in A2; the row reference changes as the formula is filled down.

A result greater than 1 means that the identifier appears more than once in the checked range. For a multi-field key, a spreadsheet's COUNTIFS function can count rows meeting several conditions. For example, if patient codes are in column A and appointment dates are in column B, the formula below counts rows with both the code and date from row 2. The range references are fixed so that they continue to cover the data when the formula is filled down.

This check flags matching patient-date pairs; it does not establish that there was only one appointment on that date. If separate appointments can occur on the same day, the key needs another suitable field, or each flagged pair needs review. Spreadsheet applications differ in function names, separators, and how they handle blank cells, so verify a formula on a small, known set of rows before relying on it.

Matching Rows Are Not Always Duplicate Events

The most important judgment is whether matching rows represent one event recorded twice or different events that happen to share values. Consider a data set with one row per daily measurement. The same person can have the same measurement on two different dates; those are separate observations. Conversely, an accidental repeated import could create two identical rows for the same person and date. The data's structure and collection process determine which explanation is plausible.

Even exact-row matches can be legitimate. Two items may have identical recorded characteristics but still be separate units, such as two products with the same category and price. If the data include a unique item identifier, it may reveal that they are distinct. If not, records should not be removed just because every currently recorded value happens to match.

A difference between rows does not always prove that both are valid either. One copy might contain a typing error in a non-key field, or the same event could have been imported with inconsistent details. Investigate the discrepancy using the source record, a collection log, or other appropriate evidence. Avoid inventing a preferred value simply because it seems more plausible.

A Short Justification for Counting Matches

Suppose a particular key value occurs \(f\) times. A frequency check returns \(f\) for each of those rows, so marking every row with frequency greater than 1 identifies all rows in that repeated group. If review establishes that only one record should remain, the number of excess rows is \(f-1\), not \(f\): one copy is retained. Across repeated groups with frequencies \(f_1,f_2,\ldots,f_k\), the total number of excess rows, if each group is confirmed to represent one record, is the sum below.

For example, if one confirmed event appears three times and another appears twice, the excess is \((3-1)+(2-1)=3\) rows. The count is a useful audit of the effect of a decision, but it is not a rule for deciding that each group is truly one event. That conclusion must come from the meaning of the key and the available evidence.

Document the Review and Its Outcome

A defensible duplicate check leaves a clear account of what was checked, what was found, and what happened next. Keep the preserved raw data unchanged, then perform the check on a working copy. Record the fields used for the match, the number of candidate rows or groups, how candidates were investigated, and the decision for each reviewed group. If rows are removed from the working data, record how many and why; if candidates are retained, note the reason when it is not self-evident.

A concise log entry could state: “Sorted by appointment ID and checked ID frequency in the working copy; two rows with ID A17 matched on all recorded fields. Source export confirmed one repeated import. Retained one row; excluded one confirmed copy.” This account makes the action understandable without implying that every repeated value was automatically deleted.

When evidence is inconclusive, do not quietly choose one row to keep. Preserve the candidates, flag the uncertainty, and seek clarification from an appropriate source or data owner. The distinction matters because duplicate handling changes the number of observations and can change later counts, averages, and other summaries.

Common Mistakes

• Deleting every row with a repeated value. A repeated person code, date, or measurement can describe distinct events. First decide what one row represents and inspect the relevant fields.
• Assuming exact matches must be errors. Separate units can share every recorded value. Use identifiers and source context to assess whether they are the same event.
• Checking the wrong fields. A key that omits a date or event identifier can collapse distinct records together. A key that includes an irrelevant field can fail to flag a duplicate with a minor transcription difference.
• Sorting only one column. This can scramble the relationship between fields in a row. Sort the entire table together, including all data columns.
• Treating a formula result as a decision. A count identifies matches under a specified rule; it does not prove that the records are accidental copies.
• Failing to record removals. Without a note about the fields, evidence, and number of rows affected, someone reviewing the cleaned data cannot tell what changed or why.