Introduction

Finding duplicates across two columns means you care about the combination of values, not whether either column contains repeats by itself. In other words, you are looking for duplicate pairs such as (John, Doe) appearing more than once, not just duplicate first names or duplicate last names separately.

Think in Terms of a Composite Key

The clean way to reason about this problem is to treat the two columns together as one composite key.

Example data:

In this table:

• 'John is duplicated in the first column'
• 'Doe may be duplicated in the second column'
• only (John, Doe) is a duplicate pair

That distinction is what the query or formula must express.

SQL Solution

In SQL, the standard pattern is GROUP BY on both columns followed by HAVING COUNT(*) > 1.

1SELECT first_name, last_name, COUNT(*) AS duplicate_count
2FROM people
3GROUP BY first_name, last_name
4HAVING COUNT(*) > 1;

This returns only combinations that occur more than once.

If you want the original rows rather than the grouped summary, join the grouped result back to the table:

1SELECT p.*
2FROM people p
3JOIN (
4    SELECT first_name, last_name
5    FROM people
6    GROUP BY first_name, last_name
7    HAVING COUNT(*) > 1
8) d
9ON p.first_name = d.first_name
10AND p.last_name = d.last_name;

That gives you every duplicate record whose two-column combination repeats.

Pandas Solution

In pandas, the same idea works with duplicated and the subset parameter:

1import pandas as pd
2
3df = pd.DataFrame({
4    "first_name": ["John", "Jane", "John", "John"],
5    "last_name": ["Doe", "Smith", "Doe", "Smith"]
6})
7
8duplicates = df[df.duplicated(subset=["first_name", "last_name"], keep=False)]
9print(duplicates)

keep=False marks all rows in each duplicate group rather than leaving one row unmarked.

If you want the frequency per pair:

1counts = (
2    df.groupby(["first_name", "last_name"])
3      .size()
4      .reset_index(name="count")
5)
6
7print(counts[counts["count"] > 1])

Spreadsheet Approach

In Excel or Google Sheets, create a helper column that combines the two values and then detect duplicates on that helper key.

Example helper formula:

=A2 & "|" & B2

After that, you can use conditional formatting or COUNTIF on the helper column:

=COUNTIF($C:$C, C2) > 1

This works, but use a separator that cannot appear naturally in the data, or choose a more robust formula if collisions are possible.

Null and Whitespace Considerations

Real datasets often fail duplicate checks because the values are not normalized.

Examples:

• 'John versus john'
• 'Doe versus DOE'
• null versus empty string

If duplicates should be case-insensitive or whitespace-insensitive, normalize first.

In SQL that may mean TRIM and LOWER. In pandas that may mean .str.strip().str.lower() before duplicate detection.

When Order Matters

Most two-column duplicate checks assume column meaning is fixed, such as (first_name, last_name). If your problem is more like pairs where order does not matter, such as (A, B) being treated the same as (B, A), you must normalize the pair first before grouping.

That is a different problem from ordinary duplicate detection.

Common Pitfalls

The biggest mistake is checking each column independently and assuming that tells you whether the pair is duplicated. It does not.

Another issue is forgetting null, whitespace, or case normalization. Two rows that are logically identical may not compare equal until cleaned.

Spreadsheet users often concatenate values without a separator, which can create collisions such as AB plus C looking the same as A plus BC.

Finally, do not forget whether you want one representative duplicate group or every row that participates in the duplicate.

Summary

• To find duplicates in two columns, treat the two values as one composite key.
• In SQL, GROUP BY col1, col2 HAVING COUNT(*) > 1 is the standard answer.
• In pandas, use duplicated(subset=[...], keep=False).
• In spreadsheets, a helper key column is usually the simplest approach.
• Normalize whitespace, case, and null behavior before checking duplicates if the data is messy.