Function Problems

How to Remove Duplicate Combinations Using Multiple Criteria in Excel

Natalie TaylorNatalie Taylor Nov 10, 2026 872 views

Question details

The user needs to extract unique data combinations across multiple specific columns without losing essential fields used for further calculations.

How to Remove Duplicate Combinations Using Multiple Criteria in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Identify your criteria columns

Determine the columns you want to combine. For example, 'EX Code' and 'Usage Type' in your 'EnergyAudit_Sorted' table.

2
Combine columns using HSTACK

Select a blank destination cell and type the HSTACK function to merge non-adjacent columns: HSTACK(EnergyAudit_Sorted[EX Code], EnergyAudit_Sorted[Usage Type]).

3
Wrap with the GROUPBY function

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.

4
Execute the formula

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 GROUPBY and HSTACK to Extract Unique Combinations
Function Availability: The GROUPBY function is available in newer versions of Microsoft 365. If your version does not support it, you can achieve the same result by using the UNIQUE function wrapped around HSTACK.
Manage Data Efficiently

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. 1. Open your dataset: Launch WPS Spreadsheet and open your workbook containing the audit rows.
  2. 2. Use advanced formulas: Apply dynamic array formulas like UNIQUE or FILTER in a blank cell to instantly pull out distinct combinations.
  3. 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.
Fully compatible with Microsoft Excel formulas and table formats.Advanced filtering and duplicate removal tools built-in.Lightweight and runs smoothly even with large datasets.Free to use with a familiar, user-friendly interface.
QA img-9

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.