How to Find Duplicates and Combine Inventory Data in Excel
Question details
The user needs to identify duplicate products with varying attributes and combine previous and purchased inventory data accurately.

- 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.
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.
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.
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.
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.
In the next column, use a similar formula =SUMIFS(F2:F100, A2:A100, G2) to calculate the total purchased stock from column F.
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.

Consolidate Data Using Power Query
Ideal for large datasets where you need to group duplicates and sum inventory automatically without writing complex formulas.
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. Open your Inventory File: Launch WPS Spreadsheet and open your existing inventory workbook.
- 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. 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.

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.




