How to Remove Duplicate Combinations Using Multiple Criteria in Excel
Question details
The user needs to extract unique data combinations across multiple specific columns without losing essential fields used for further calculations.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Consolidating energy audit rows by distinct combinations of specific columns, such as EX Code, PR Code, and Usage Type.
- Observed behavior
- Needs to filter out duplicate rows based on multiple criteria columns simultaneously while retaining the original data relationships.
Ensure your dataset is formatted as an Excel Table (e.g., named 'EnergyAudit_Sorted') so that dynamic array formulas update automatically when new data is added.
Use GROUPBY and HSTACK to Extract Unique Combinations
This is the most efficient method to return distinct rows based on non-adjacent columns without calculating unwanted subtotals.
The GROUPBY function, when combined with HSTACK, allows you to pull arrays of non-adjacent columns together and group them to find unique combinations seamlessly.
Determine the columns you want to combine. For example, 'EX Code' and 'Usage Type' in your 'EnergyAudit_Sorted' table.
Select a blank destination cell and type the HSTACK function to merge non-adjacent columns: HSTACK(EnergyAudit_Sorted[EX Code], EnergyAudit_Sorted[Usage Type]).
Enclose the HSTACK formula within a GROUPBY function to return only the unique rows: =GROUPBY(HSTACK(EnergyAudit_Sorted[EX Code],EnergyAudit_Sorted[Usage Type]),,,,0). The final '0' ensures that no totals or subtotals are generated.
Press Enter. The dynamic array will spill the unique combinations into the adjacent cells. To change criteria, simply replace the column names inside the formula, such as swapping 'Usage Type' for 'PR Code'.

Use FILTER with Multiplication for AND Logic
Use this method when you need to extract entire records that match a specific combination of criteria rather than just summarizing the unique combinations.
Consolidate and Filter Data Easily with WPS Spreadsheet
WPS Spreadsheet provides powerful data filtering, consolidation tools, and dynamic array functions to help you manage complex datasets like energy audits effortlessly.
- 1. Open your dataset: Launch WPS Spreadsheet and open your workbook containing the audit rows.
- 2. Use advanced formulas: Apply dynamic array formulas like UNIQUE or FILTER in a blank cell to instantly pull out distinct combinations.
- 3. Remove duplicates visually: Alternatively, navigate to the 'Data' tab and click 'Remove Duplicates', selecting the specific columns (like EX Code and PR Code) to consolidate your data without complex formulas.

Frequently Asked Questions
Can I use the UNIQUE function instead of GROUPBY?
Yes, you can use =UNIQUE(HSTACK(range1, range2)) to get unique combinations across multiple columns. GROUPBY is particularly useful if you decide to add aggregations later, but UNIQUE is perfect for strictly removing duplicates.
Why is multiplication (*) used in the FILTER function?
In Excel array formulas, multiplying two logical conditions evaluates to 1 (TRUE) only if both conditions are TRUE. This acts exactly like an 'AND' operator, ensuring the FILTER function returns rows that meet every specified criterion.
What happens if I add new data to my audit rows?
If your data is formatted as an official Excel Table, any dynamic array formulas referencing that table, such as FILTER or GROUPBY, will automatically expand and update their results to include the newly added rows.




