Answer

Removing duplicates based on criteria means filtering rows that repeat specific values while keeping only the rows that match a defined rule. Excel uses built-in tools and formulas to perform this task with consistent precision. Excel provides a direct feature for removing duplicate rows when a single rule or multiple rules define uniqueness.

Steps

1. Select the full range that contains the data.

2. Go to Data on the Ribbon.

3. Choose Remove Duplicates.

4. Select the columns that define the criteria.

5. Confirm with OK.

Key Details

• Windows and Macbook use the same steps.

• Remove Duplicates keeps the first occurrence and deletes the remaining entries.

• Excel evaluates only visible cells when filtering is active.

How to remove duplicates based on multiple criteria using formulas

Formulas create dynamic lists that update as data changes. LaTeX formulas appear when required.

Create a list without duplicates

Use UNIQUE to extract distinct rows that meet defined criteria.

\text{=UNIQUE(FILTER(A2:D100, B2:B100="Active"))}

This formula returns unique rows from A2:D100 when column B equals “Active.”

Identify duplicates based on criteria

Use COUNTIFS to count how many times a combination appears.

  \text{=COUNTIFS(A:A,A2, B:B,B2)}

A result greater than 1 means the row duplicates another row that shares the same values in columns A and B.

How to remove duplicates based on criteria with Advanced Filter

Advanced Filter gives more control when criteria use AND or OR logic without writing formulas inside the main table.

Steps

1. Create a criteria range above or beside the table.

2. Enter a header identical to the column header.

3. Enter the condition below the header.

4. Select the full dataset.

5. Open Data > Advanced.

6. Choose Filter in place or Copy to another location.

7. Check Unique records only.

8. Apply the filter.

Use Cases

• Remove duplicates only when a status matches a specific value.

• Create a unique list where only rows above a numeric threshold appear.

How to remove duplicates based on the earliest or latest date

A helper formula ranks entries and selects the first or last occurrence according to criteria.

Steps

1. Add a column that generates a ranking using a date column.

2. Filter to keep only rank 1.

3. Remove or hide helper columns.

Formula Example

   \text{=IF(COUNTIFS(A:A,A2)>1, RANK(E2, FILTER(E:E, A:A=A2)), 1)}

This ranks dates in column E for each value in column A.

How to remove duplicates based on criteria with Power Query

Power Query handles structured transformations efficiently.

Steps

1. Select the data.

2. Go to Data > Get & Transform Data > From Table/Range.

3. Load data into Power Query.

4. Apply a filter to keep rows that meet your criteria.

5. Right-click the columns that define uniqueness.

6. Select Remove Duplicates.

7. Load the cleaned data back into the worksheet.

Advantages

• Power Query produces repeatable processes.

• Transformations stay stored as steps that can be refreshed.

Practical criteria examples

• Keep only duplicates where a value appears more than twice.

• Remove duplicates when a status equals “Approved.”

• Keep the duplicate with the highest sales value.

• Remove duplicates in one column while preserving unique combinations in another.

Common mistakes and how to avoid them

1. Selecting a single column when uniqueness depends on multiple columns.

2. Forgetting that Remove Duplicates deletes data permanently.

3. Using formulas without converting ranges to tables for dynamic growth.

4. Applying criteria incorrectly when the table contains merged cells.

5. Applying Remove Duplicates on filtered data without selecting visible rows only.

Excel removes duplicates successfully when the criteria define exactly how rows should be evaluated.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report