Cleanup formula page
Duplicate Rows by Two Columns Formula
Use this when one column is not enough to define a duplicate, such as email plus region.
Find duplicate rows by two columns.
Rows with the same Email and Region combination are flagged.
Copy formulas
=IF(COUNTIFS($A$2:$A$100, A2, $B$2:$B$100, B2)>1, "Duplicate row", "")=IF(COUNTIFS($A$2:$A$100, A2, $B$2:$B$100, B2)>1, "Duplicate row", "")Excel and Google Sheets use the same formula syntax for this pattern.
Example data
| Region | Status | |
|---|---|---|
| maya@example.com | East | Active |
| noah@example.com | West | Active |
| maya@example.com | East | Active |
| West | Inactive |
Formula explanation
- COUNTIFS checks both columns on the same row set.
- Only repeated combinations are flagged.
- Add a blank guard if either key column can be empty.
What this formula does
Duplicate Rows by Two Columns Formula is built around IF. The example formula on this page is =IF(COUNTIFS($A$2:$A$100, A2, $B$2:$B$100, B2)>1, "Duplicate row", ""), and it is written against sample columns such as Email, Region, Status.
Excel and Google Sheets use the same copy pattern here, so the main work is adjusting ranges, criteria, and output cells to match your sheet.
Cleanup formulas are usually dynamic array or row-check formulas. They are best used in helper columns or clean output areas where spilled results will not overwrite existing data.
Use this pattern for this task: Find duplicate rows by two columns. Check blanks, hidden spaces, and spill space before assuming the formula is wrong.
| Syntax piece | Role in the formula |
|---|---|
| COUNTIFS | COUNTIFS checks both columns on the same row set. |
| Part 2 | Only repeated combinations are flagged. |
| Part 3 | Add a blank guard if either key column can be empty. |
Practical use cases
=IF(COUNTIFS($A$2:$A$100, A2, $B$2:$B$100, B2)>1, "Duplicate row", "")Excel: Use this in a helper column or clean output area to audit imported rows before they feed a report. Returns: Rows with the same Email and Region combination are flagged.
=IF(COUNTIFS($A$2:$A$100, A2, $B$2:$B$100, B2)>1, "Duplicate row", "")Google Sheets: Use this before deduping, importing, or sharing a spreadsheet so suspicious rows are visible and easy to review. Returns: Rows with the same Email and Region combination are flagged.
Common errors
- Blank rows and hidden spaces can change duplicate or unique counts.
- Dynamic array formulas need empty output space to spill results.
- Case-sensitive cleanup needs different formulas than normal COUNTIF or UNIQUE patterns.
Common errors and troubleshooting
| Issue | Likely cause | Fix |
|---|---|---|
| Copied formula gives an unexpected result | Blank rows and hidden spaces can change duplicate or unique counts. | Check blanks, hidden spaces, duplicate keys, and available spill space before changing the formula. |
| Common setup problem | Dynamic array formulas need empty output space to spill results. | Check blanks, hidden spaces, duplicate keys, and available spill space before changing the formula. |
| Common setup problem | Case-sensitive cleanup needs different formulas than normal COUNTIF or UNIQUE patterns. | Check blanks, hidden spaces, duplicate keys, and available spill space before changing the formula. |
| Dynamic array will not spill | Cells below or to the right of the formula are not empty. | Move the formula to a clear output area or delete blocking values before expecting the result to expand. |
| Duplicate or unique result looks wrong | Blank cells, hidden spaces, or case differences make values that look identical behave differently. | Add TRIM or a blank guard when needed, and decide whether case-sensitive matching is required for the audit. |
When not to use this formula
- Do not use cleanup formulas as a replacement for source-system data fixes when the same issue keeps returning.
- Do not use dynamic array cleanup formulas in older Excel versions that do not support spilling.
Alternatives
| Alternative | When to use it |
|---|---|
| Duplicate Checker Formula Builder | Use for configurable duplicate flags with blank guards. |
| Count Unique Formula Builder | Use for distinct counts with blank handling. |
| CSV Column Cleaner | Use before importing messy CSV headers. |
Compare with related formulas
| Formula or tool | How it differs | Use it instead when |
|---|---|---|
| Duplicate Checker Formula Builder | Duplicate Checker Formula Builder solves a nearby workflow, while Duplicate Rows by Two Columns Formula is focused on find duplicate rows by two columns. | Use for configurable duplicate flags with blank guards. |
| Count Unique Formula Builder | Count Unique Formula Builder solves a nearby workflow, while Duplicate Rows by Two Columns Formula is focused on find duplicate rows by two columns. | Use for distinct counts with blank handling. |
| CSV Column Cleaner | CSV Column Cleaner solves a nearby workflow, while Duplicate Rows by Two Columns Formula is focused on find duplicate rows by two columns. | Use before importing messy CSV headers. |
Test cases
| Test | Platform | Formula | Returns |
|---|---|---|---|
| Excel copy output | Excel | =IF(COUNTIFS($A$2:$A$100, A2, $B$2:$B$100, B2)>1, "Duplicate row", "") | Rows with the same Email and Region combination are flagged. |
| Google Sheets copy output | Google Sheets | =IF(COUNTIFS($A$2:$A$100, A2, $B$2:$B$100, B2)>1, "Duplicate row", "") | Rows with the same Email and Region combination are flagged. |
Related formulas
FAQ
What does the Duplicate Rows by Two Columns Formula do?
It supports this task: Find duplicate rows by two columns. The example uses $A$2:$A$100, but you should replace the ranges and criteria with the cells in your own workbook.
Does the Duplicate Rows by Two Columns Formula work in Excel and Google Sheets?
Yes. The Excel and Google Sheets versions on this page use the same syntax for this pattern.
What should I change before copying the formula?
Change the source ranges, criteria cells, and output range references so they match your sheet. Keep related ranges on the same rows unless the formula notes say otherwise.
Why does the cleanup result spill into nearby cells?
Dynamic array formulas expand into the cells they need. Keep the output area empty so the formula can spill.
Should blanks be included in this cleanup formula?
Most cleanup formulas on this site exclude blanks or include a blank guard. Adjust that only if blank values are meaningful in your audit.
Can hidden spaces affect duplicate or unique results?
Yes. Values that look identical can be different if one contains a leading, trailing, or nonbreaking space.