How to merge an old and new list and flag new, removed and changed rows

What you're building

Price lists, staff lists, product catalogues and customer exports all change between versions. Rather than two separate checks for “what's missing” and “what's new”, a single merged list with a status column answers both, plus which rows changed. This is a full outer join: every ID from either list appears once.

Check: 4 old rows + 1 new − 1 removed = 4 new rows. If that sum fails, there are duplicate IDs.

Excel 365 and Google Sheets formulas

1. Build the unique list of IDs from both sheets in A2: =SORT(UNIQUE(VSTACK(Old!A2:A500, New!A2:A500))). Blank rows in the ranges add a blank ID; wrap the ranges with FILTER(range, range<>"") or trim the ranges. In Google Sheets use {Old!A2:A500; New!A2:A500} in place of VSTACK.
2. Old price in B2: =XLOOKUP(A2#, Old!A2:A500, Old!C2:C500, ""). New price in C2: the same against the New sheet. (In Google Sheets, use A2:A instead of the spill reference A2#.)
3. Status in D2: see the formula below.

=IFS(COUNTIF(Old!A:A, A2)=0, "New",
     COUNTIF(New!A:A, A2)=0, "Removed",
     B2<>C2, "Changed",
     TRUE, "Unchanged")

Testing presence with COUNTIF rather than a blank price matters: a product whose price is genuinely blank would otherwise look new or removed. To compare several columns at once, compare a joined string such as Old product&"|"&Old price against the same for New, or add one Changed flag per column so you know which field moved.

In older Excel, build the ID list by pasting both ID columns one under the other and using Data → Remove Duplicates, then use the same lookups. For a cell-by-cell comparison of sheets with the same layout instead, see comparing two Excel sheets for differences.

SQL: FULL OUTER JOIN with a CASE

SELECT COALESCE(o.sku, n.sku) AS sku,
       o.price AS old_price,
       n.price AS new_price,
       CASE
         WHEN o.sku IS NULL THEN 'New'
         WHEN n.sku IS NULL THEN 'Removed'
         WHEN o.price IS DISTINCT FROM n.price
           OR o.product IS DISTINCT FROM n.product THEN 'Changed'
         ELSE 'Unchanged'
       END AS status
FROM old_list o
FULL OUTER JOIN new_list n ON o.sku = n.sku
ORDER BY sku;

IS DISTINCT FROM treats two NULLs as equal and NULL versus a value as different, which a plain <> doesn't. Then summarise: SELECT status, count(*) FROM … GROUP BY status. Analistable answers “what's new, removed or changed between these two lists?” with this pattern, running the query in your browser against both files.

Power Query

Merge Queries with join kind Full Outer on the ID, expand the second table's columns, then add a Custom Column: if [SKU] = null then "New" else if [New.SKU] = null then "Removed" else if [Price] <> [New.Price] then "Changed" else "Unchanged". Column names depend on how you expand. The result refreshes when either source updates. See Power Query merge join kinds.

Common traps

• IDs that changed format: “A-100” vs “A100” or numbers stored as text. Every one shows as one Removed plus one New. Normalise IDs first.
• Duplicate IDs: if an ID appears twice in a list, the lookups pick the first row and the SQL join multiplies rows. Check with COUNTIF > 1.
• Rounding: 24 and 24.000001 count as Changed. Round prices before comparing.
• Whitespace in text fields: trailing spaces make an unchanged product name look changed. TRIM both sides.

If you only need to know which items appear in both lists, finding matches between two lists is simpler. More options are in the compare spreadsheets guide.

Reporting the changes

Once every row has a status, a short summary is what most people actually read. Count rows by status with =COUNTIF(D:D, "New") and so on, or a pivot with Status in rows. For price lists, add the value of the change: the sum of New price minus Old price for Changed rows shows whether the update raised or lowered prices overall.

Five rows is the number of distinct SKUs across both lists. Keep the merged sheet as the audit record of the update, and filter it to New and Removed when someone asks what was added or dropped.

Frequently asked questions

Is this the same as comparing two sheets cell by cell?

No. A cell-by-cell comparison needs both sheets in the same row order. Matching on an ID works even when rows were added, removed or re-sorted between versions.

Can Google Sheets flag new and removed rows?

Yes. Use UNIQUE over both ID ranges stacked with {range1; range2}, then COUNTIF against each list and IFS for the status, as in Excel.

How do I find rows that were removed from a list in Excel?

Look each old ID up in the new list with =COUNTIF(New!A:A, A2)=0, or =ISNA(XLOOKUP(...)). TRUE means the row is in the old list only.

What is a full outer join?

A join that keeps every row from both tables, matching where it can and leaving blanks where a row exists on one side only.

How do I flag which column changed?

Add one comparison per column, for example =IF(B2<>E2, "Price", ""), and join the flags with TEXTJOIN for a single “changed fields” column.

Why do my counts not add up?

Usually duplicate IDs in one list, or IDs whose format differs between versions. Fix those and old + new − removed will equal the new row count.

Related guides

• How to compare two Excel sheets for differences
• How to find matches between two lists in Excel
• How to compare two spreadsheets for differences and matches
• How to merge two spreadsheets and remove duplicates
• Price comparison template