Understanding the Goal: "Replacing" Duplicates

When we talk about "replacing" duplicates while creating a unique list, we usually mean one of three things:

• Deduplication with Priority: Keeping only the first or last occurrence of a duplicate record based on a criteria like timestamp or version number.
• Deduplication with Aggregation: Replacing duplicate rows by consolidating them (e.g., summing sales for duplicate product entries).
• Dynamic Filtering: Extracting a unique list on the fly using formulas, ensuring that as raw data updates, the unique list adapts automatically.

Historically, Excel users relied on the manual "Remove Duplicates" tool or complex array formulas. Today, with Excel 365 and Excel 2021, dynamic array functions like UNIQUE, FILTER, SORT, and XLOOKUP make this process dynamic, robust, and incredibly efficient.

Method 1: Basic Unique List Generation with the UNIQUE Function

If your goal is simply to extract unique rows and discard exact duplicates across all selected columns, Excel's built-in UNIQUE function is the easiest solution.

The Formula:

=UNIQUE(A2:C100)

How it works:

This formula scans the range A2:C100 and returns a list of rows that are completely unique. If two rows have identical values in columns A, B, and C, only the first occurrence is returned, effectively "replacing" the subsequent duplicates with a single, clean instance.

Method 2: Keeping the Latest Duplicate Row (Deduplication with Priority)

A highly common scenario is having a log of transactions or status updates where a customer or product ID appears multiple times. You want a unique list of IDs, but you must "replace" the duplicates by keeping only the most recent entry based on a Date or Timestamp column.

The Scenario:

Imagine a dataset in range A2:C11:

We want a unique list of Product IDs showing only their latest status.

The Formula Solution (Excel 365 / 2021):

To solve this dynamically, we can combine UNIQUE, SORT, and XLOOKUP. Let's write this cleanly using the LET function to improve readability and performance:

=LET(
    DataRange, A2:C11,
    SortedData, SORT(DataRange, 1, -1),
    UniqueProducts, UNIQUE(INDEX(SortedData, 0, 2)),
    LatestStatus, XLOOKUP(UniqueProducts, INDEX(SortedData, 0, 2), INDEX(SortedData, 0, 3)),
    CHOOSECOLS(HSTACK(UniqueProducts, LatestStatus), 1, 2)
)

Formula Breakdown:

• DataRange: Defines the raw data area.
• SortedData: Sorts the entire dataset by the first column (Date) in descending order (-1). This ensures that the newest entries are positioned at the very top of the list.
• UniqueProducts: Extracts a unique list of Product IDs from the second column (Column B) of the sorted data.
• LatestStatus: Uses XLOOKUP to search for each unique product ID within the sorted list. Because the list is sorted in descending order, XLOOKUP naturally grabs the first match it finds, which corresponds to the latest date.
• CHOOSECOLS / HSTACK: Combines the unique product IDs and their corresponding latest statuses into a neat, two-column array.

Method 3: Consolidating and Replacing Duplicates by Summing Values

Sometimes, "replacing" duplicates means merging duplicate rows and adding up their quantities or sales figures. While Pivot Tables are great for this, dynamic formulas offer a zero-click, real-time update alternative.

The Formula:

Assume your products are in B2:B100 and sales values are in C2:C100. To generate a unique product list in column E and place their summed values in column F:

Step 1: Extract unique list in cell E2:

=UNIQUE(B2:B100)

Step 2: Sum matching values in cell F2 using a spilled array formula:

=SUMIFS(C2:C100, B2:B100, E2#)

Note: The # symbol after E2 is the spill range operator. It tells Excel to apply the SUMIFS calculation to every single row generated by the unique list in cell E2 automatically.

Method 4: The Advanced All-in-One Solution using GROUPBY (Excel Beta/Latest Version)

Microsoft recently introduced the GROUPBY function, which simplifies the process of replacing duplicate rows with consolidated calculations down to a single formula.

The Formula:

=GROUPBY(B2:B100, C2:C100, SUM, 3, 1)

Arguments Breakdown:

• Row fields (B2:B100): The column containing duplicates that you want to make unique.
• Values (C2:C100): The column you want to aggregate.
• Function (SUM): The math function used to replace/merge duplicate entries (you could also use AVERAGE, MAX, or MIN).
• Headers (3): Automatically detects and displays headers.
• Total depth (1): Determines if grand totals are shown.

Method 5: Legacy Solution for Older Excel Versions (Excel 2019 and Prior)

If you are working on an older version of Excel that lacks dynamic arrays (like UNIQUE), you must rely on array formulas combined with INDEX, MATCH, and COUNTIF.

Step 1: Extracting Unique Values (Enter in E2 and drag down):

Enter the following formula as an array formula (press Ctrl + Shift + Enter in Excel 2019 or older):

=IFERROR(INDEX($B$2:$B$100, MATCH(0, COUNTIF($E$1:E1, $B$2:$B$100), 0)), "")

Make sure the cell reference $E$1:E1 refers to the cell directly above the first formula cell. As you drag this down, it tracks which values have already been extracted and ignores them, creating a clean, unique list.

Step 2: Replacing duplicates with their corresponding values:

Once you have the unique list in column E, you can use standard lookup functions like VLOOKUP or INDEX/MATCH to fetch related records from your table. If duplicates exist, standard VLOOKUP will always return the first match, effectively replacing duplicates with the first occurrence.

Pro-Tip: Keep Your Source Data in an Excel Table

Regardless of which formula you choose, it is highly recommended to format your raw data as an Excel Table (shortcut: Ctrl + T). Doing so converts static ranges (like A2:C100) into structured references (like Table1[#Data]).

When you add new records or delete rows from an Excel Table, your dynamic unique formulas will automatically resize to match the new boundaries, eliminating the risk of empty cells or omitted data in your reports.

Summary of Approaches