Data Import & Export

How to Find Duplicates and Combine Inventory Data in Excel

Natalie TaylorNatalie Taylor Nov 10, 2026 870 views

Question details

The user needs to identify duplicate products with varying attributes and combine previous and purchased inventory data accurately.

How to Find Duplicate Products and Combine Inventory Data in Excel
Product
Excel
Device & OS
not provided
Scenario
Managing product inventory and consolidating stock data from different periods.
Observed behavior
Products appear multiple times with the same description but different prices or categories, requiring consolidation of last month's inventory with newly purchased stock.
Before you start

Ensure your data is organized in a tabular format with clear column headers (e.g., Product Name, Description, Category, Price, Previous Inventory, Purchased Quantity) before applying formulas.

Solution 1Recommended

Use UNIQUE and SUMIFS to Consolidate Inventory

Extract a unique list of products and sum their corresponding inventory quantities using built-in Excel functions.

This method is ideal if you want a dynamic formula-based approach. The UNIQUE function will filter out duplicate product names, and SUMIFS will aggregate the inventory counts for each unique item.

1
Extract Unique Products

Click on an empty cell where you want your consolidated list to start and enter the formula =UNIQUE(A2:A100) (assuming column A contains your product names) to generate a list without duplicates.

2
Sum Previous Inventory

In the adjacent column, enter the formula =SUMIFS(E2:E100, A2:A100, G2) where E is the Previous Inventory column, A is the Product Name column, and G2 is the first unique product generated in the previous step.

3
Sum Purchased Inventory

In the next column, use a similar formula =SUMIFS(F2:F100, A2:A100, G2) to calculate the total purchased stock from column F.

4
Calculate Total Inventory

Add a final column that simply adds the two SUMIFS results together (e.g., =H2+I2) to get the combined total inventory for each product.

Use UNIQUE and SUMIFS to Consolidate Inventory
Data Consistency: Ensure that product names are spelled exactly the same way and do not contain extra spaces; otherwise, the formulas will treat them as distinct items.
Manage Inventory Efficiently

Consolidate Inventory Data Easily with WPS Spreadsheet

WPS Spreadsheet offers powerful data consolidation tools, including advanced formulas like SUMIFS and UNIQUE, as well as intuitive pivot tables to help you manage inventory without hassle.

  1. 1. Open your Inventory File: Launch WPS Spreadsheet and open your existing inventory workbook.
  2. 2. Use the Consolidate Tool: Go to the 'Data' tab and click on 'Consolidate' to quickly sum up inventory quantities for duplicate product names based on column headers.
  3. 3. Apply Advanced Formulas: Alternatively, utilize the built-in SUMIFS formulas or insert a Pivot Table from the 'Insert' tab to group and calculate total stock dynamically.
Fully compatible with Microsoft Excel formats (.xlsx, .xls, .csv).Built-in advanced functions like UNIQUE, XLOOKUP, and SUMIFS for flawless inventory management.Lightweight and fast, handling large inventory datasets smoothly without lagging.Free to use with a familiar, user-friendly interface that requires no learning curve.
microsoft office alternative - wps office

Frequently Asked Questions

How can I highlight duplicate products before combining them?

Select your product name column, go to the 'Home' tab, click 'Conditional Formatting', choose 'Highlight Cells Rules', and select 'Duplicate Values'. This will color-code all recurring items so you can review them visually.

Why is my SUMIFS formula returning zero for some products?

This usually happens if there are hidden characters, trailing spaces, or slight spelling differences in the product names. Use the TRIM function (e.g., =TRIM(A2)) in a helper column to clean your text data before applying SUMIFS.

Can I use a Pivot Table to combine inventory data instead of formulas?

Yes, a Pivot Table is an excellent, formula-free way to consolidate data. Go to the 'Insert' tab, click 'PivotTable', drag the 'Product Name' field to the Rows area, and drag both 'Previous Inventory' and 'Purchased Inventory' to the Values area to automatically sum them up.