Forum Discussion
Removing Duplicates from an Array based on Multiple Criteria
I have since found a solution, described here: https://www.exceldemy.com/excel-filter-multiple-criteria/
The correct syntax for the formula I showed earlier would be:
=FILTER(EnergyAudit_Sorted[[Usage Type]:[EX Code]], ('Energy Audit - Sorted'!EXCode=$D2) * (EnergyAudit_Sorted[Usage Type]=$E2))
The '*' operator performs the AND function in this case.
One way to remove dupes while considering multiple columns is to use GROUPBY like this:
=GROUPBY(HSTACK(EnergyAudit_Sorted[EX Code],EnergyAudit_Sorted[Usage Type]),,,,0)
All you need to do is provide the columns in the rows argument of GROUPBY. HSTACK is used because those two columns aren't next to each other. The commas indicate arguments are being skipped. The '0' supresses the totals. The results are sorted A-Z by default.
Here's another example for EX Code and PR:
=GROUPBY(HSTACK(EnergyAudit_Sorted[EX Code],EnergyAudit_Sorted[PR Code]),,,,0)I'm not entirely certain of the desired return but this may give you enough to go on.
Thanks for your input, I'll try to work with those functions!
To clarify the desired return:
- A row where ALL THREE values (EX Code, PR Code, and Usage Type) are identical would be considered a duplicate row.
- Any other row, where less than all three values match, would be unique.
For example:
There are (5) rows for which EX Code = "EW4232N", Usage Type = "Class (HS)", and PR Code = "4TLED10C-2". Those would be condensed into (1) row.
There are (9) rows for which EX Code = "EW4232N", Usage Type = "Storage (Active)", and PR Code = "4TLED10C-2". This would be condensed into (1) row but would be separate from the row where the Usage Type was "Class (HS)".