Excel: Remove duplicate on one column based on text in another column

Understanding the Limitation

The built-in Remove Duplicates tool in Excel does not natively support conditional logic like "keep the row where Column B contains 'Approved'." It simply keeps the first occurrence it encounters when scanning from top to bottom. Therefore, the strategy relies entirely on controlling which row is considered the "first" through sorting.

Method 1: The Sort and Remove Approach

This method is best for one-time cleanup tasks or when you can accept a static result. It requires no coding and works in all versions of Excel including 2010.

1. Select Your Data Range: Highlight the entire dataset, including headers. Ensure you do not leave out any columns that need to be preserved alongside the rows you intend to keep.
2. Open the Sort Dialog: Go to the Data tab on the ribbon and click Sort. This opens the full sort dialog box rather than the quick sort buttons.
3. Add Priority Level: Click Add Level. In the first level, sort by the column containing the unique identifiers (e.g., ID or Name). In the second level, sort by the column containing the text criteria (e.g., Status).
4. Define Order: For the status column, choose Z to A if you want specific text like "Final" or "Active" to appear at the top, or A to Z depending on your preference. The key is that the version you want to keep must end up in the first row of each duplicate group.
5. Execute Removal: With the data still selected, go back to the Data tab and click Remove Duplicates. Uncheck all columns except the one you want to deduplicate against. Click OK.

Excel will scan down the list. Because you sorted the desired values to the top, the first time it sees a duplicate ID, it keeps that row. When it sees the same ID again later in the list, it deletes the subsequent rows. This effectively filters your data based on the secondary text criteria.

Method 2: Using VBA for Dynamic Control

If you frequently need this functionality or require more complex logic, a VBA macro provides greater flexibility. This approach allows you to specify exactly which value to prioritize without manually sorting every time.

Sub KeepSpecificDuplicate()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    
    ' Define the range assuming headers in row 1
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' Sort by ID (Col A) then by Status (Col B) descending
    ' Assuming "Keep" is alphabetically higher than "Discard" or similar
    ws.Range("A1:B" & lastRow).Sort _
        Key1:=ws.Range("A1"), Order1:=xlAscending, _
        Key2:=ws.Range("B1"), Order2:=xlDescending, _
        Header:=xlYes
        
    ' Now remove duplicates based on Column A only
    ws.Range("A1:B" & lastRow).RemoveDuplicates Columns:=Array(1), Header:=xlYes
End Sub

This script automates the manual steps described above. It sorts the data internally before running the removal command. You can modify the Order2 parameter or add additional sort keys if your criteria involve more than two columns. Note that this modifies the sheet directly, so always backup your data before running macros.

Handling Complex Scenarios

Sometimes simple sorting isn't enough. For instance, you might want to keep the row with the most recent date among duplicates. In such cases, you can use a helper column with formulas. Create a new column next to your data and use a formula like =COUNTIFS(A:A, A2, B:B, ">="&B2) to rank occurrences. Filter for the lowest rank (usually 1) and delete the rest. This method is slower on large datasets but offers precise control over which record survives the duplication check.

Another consideration is preserving related data. If your spreadsheet has multiple columns beyond the two involved in the logic, ensure they are included in the selection during the sort and removal phases. Losing track of associated data is a common error when focusing solely on the primary and secondary columns. For more advanced data manipulation techniques, such as retrieving data from multiple cells across different sheets, you might find our guide on retrieving data from multiple cells on different spreadsheets useful for expanding your toolkit.

Troubleshooting Common Issues

If the removal doesn't seem to work as expected, check for hidden characters. Text in one column might look identical but contain trailing spaces or non-breaking spaces, causing Excel to treat them as unique. Use the TRIM function to clean the data before applying the removal tool. Additionally, verify that your data is formatted consistently; mixed number formats can sometimes interfere with exact matching.

Frequently Asked Questions

Can I keep the row with the latest date instead of the first?

Yes, but you cannot do this directly with the standard Remove Duplicates button. You must first sort your data by the duplicate column ascending and the date column descending. This places the newest record at the top of each group, allowing the standard removal tool to keep it.

What happens to the other columns when I remove duplicates?

Entire rows are removed, not just cells. Any data in other columns associated with the duplicate row will also be deleted. Always review your data structure to ensure that deleting a row won't lose critical information needed elsewhere in your analysis.

Is there a formula-only way to filter duplicates?

You can use the COUNTIF function to identify duplicates. Create a helper column with =COUNTIF($A$2:A2, A2)>1 to flag subsequent occurrences. Filter this column for TRUE and delete those rows. This leaves the first occurrence intact without altering the original order significantly.

How do I handle case-sensitive duplicates?

Excel's Remove Duplicates tool is not case-sensitive by default; "Apple" and "apple" are treated as duplicates. To make it case-sensitive, you would need to use a VBA solution that iterates through the range and compares strings using VBA.StrComp with the vbBinaryCompare argument.

Does this method work in Google Sheets?

Google Sheets has a similar "Remove Duplicates" option under the Data menu. However, it also lacks native conditional logic for keeping specific duplicates. You must rely on the same sorting technique: sort by the priority column descending before removing duplicates to achieve the same result.