基于指定列重复值删除Excel行（保留首次出现项）

Got it, let's break down how to solve this problem—you need to delete rows where Column N has duplicate values, keeping only the first occurrence, and since your data updates daily (so row numbers are always changing), we need solutions that adapt automatically. Here are a few reliable methods:

Method 1: Excel's Built-in "Remove Duplicates" (Quick Manual Approach) #

This is perfect if you don't mind a few clicks each day:

• Select your entire data range: Click the header of Column A, hold Shift, then click Column N's header to select all columns from A to N. Alternatively, press Ctrl+A if your data is contiguous with no blank rows/columns in between.
• Go to the Data tab in the top menu bar.
• Click the Remove Duplicates button.
• In the pop-up dialog, uncheck all columns except Column N (make sure only N is selected).
• Click OK—Excel will instantly remove all rows where Column N has a duplicate, leaving only the first occurrence of each value.
• Pro tip: This works no matter how many rows you add daily; just re-run the steps after updating your data.

Method 2: Dynamic Array Formula (Auto-Updating for Excel 365/2021) #

If you have a modern Excel version with dynamic array support, this formula will automatically update as your data grows:

• Pick a blank cell outside your data (e.g., cell O1) and enter:

  =UNIQUE(A:N, FALSE, FALSE)
  

  Let's break this down:
  • A:N = your full data range
  • First FALSE = check for duplicates by row (not column)
  • Second FALSE = keep the first occurrence of each duplicate row
• The formula will spill into the cells below and to the right, automatically including new rows added to A:N later.

For Older Excel Versions (No Dynamic Arrays) #

Use a helper column to flag duplicates, then filter and delete:

• In cell O1, type Is Duplicate (this is your header).
• In cell O2, enter this formula:

  =COUNTIF($N$2:$N2, $N2) > 1
  

  This counts how many times the current row's Column N value has appeared from row 2 up to the current row. If it's greater than 1, it marks the row as a duplicate.
• Double-click the small square at the bottom-right of cell O2—Excel will automatically fill the formula down to the last row with data in Column N.
• Go to the Data tab, click Filter, then use the filter dropdown in Column O to select only TRUE (these are the duplicate rows to delete).
• Select all the filtered rows, right-click, and choose Delete Row. Then turn off the filter to see your cleaned data.

Method 3: VBA Macro (One-Click Automation) #

If you need to do this daily, a macro will save you time. Here's a simple script that automatically detects the last row of data:

Sub RemoveDuplicatesByColumnN()
    ' Define the full data range from A1 to the last row with data in Column N
    Dim dataRange As Range
    Set dataRange = ThisWorkbook.ActiveSheet.Range("A1:N" & ThisWorkbook.ActiveSheet.Cells(Rows.Count, "N").End(xlUp).Row)
    
    ' Remove duplicates based on Column N (column index 14), keep first occurrence
    ' Use Header:=xlNo if your data doesn't have a header row
    dataRange.RemoveDuplicates Columns:=14, Header:=xlYes
End Sub

• How to use:
  1. Press Alt+F11 to open the VBA Editor.
  2. Right-click your workbook in the Project Explorer, select Insert → Module.
  3. Paste the code above into the module.
  4. Go back to Excel, press Alt+F8, select RemoveDuplicatesByColumnN, and click Run.
• This macro will adapt to new rows automatically—no need to adjust row numbers when your data updates.

Hope one of these methods fits your workflow! Let me know if you need clarification on any step.

内容的提问来源于stack exchange，提问作者Sahana G