Data Cleaning & Audit
Find Common Duplicate Values Appearing in Both Columns (CRM vs ERP Audit)
Reconcile two invoice or SKU columns to flag records present in both lists versus missing from the second list using COUNTIF and dynamic FILTER.
Cross-check two system exports without sorting or manual VLOOKUP #N/A cleanup
During month-end close, accounting reconciles CRM booked order IDs in column A against ERP posted invoice IDs in column B. An ID appearing in both columns is reconciled ("Matched in Both"), while an ID with zero hits in column B is an unbilled exception.
On each row, COUNTIF($B$2:$B$5, A2) > 0 returns TRUE whenever A2 exists anywhere in column B, and FILTER(A2:A6, (A2:A6=B2)+(A2:A6=B3)+(A2:A6=B4)+(A2:A6=B5) > 0, "None") extracts the exact cross-column intersection.
| CRM Order ID (Col A) | ERP Invoice ID (Col B) | COUNTIF($B$2:$B$5, A_i) | Reconciliation Status |
|---|---|---|---|
| INV-8801 | INV-8803 | 1 | Matched in Both |
| INV-8802 | INV-8801 | 0 | CRM Only (Unbilled) |
| INV-8803 | INV-8809 | 1 | Matched in Both |
| INV-8804 | INV-8805 | 0 | CRM Only (Unbilled) |
| INV-8805 | — | 1 | Matched in Both |
Extracted List of IDs Present in Both Columns
Filters CRM IDs in A2:A6 that also appear in ERP range B2:B5.
=TEXTJOIN(", ", TRUE, FILTER(A2:A6, (A2:A6 = B2) + (A2:A6 = B3) + (A2:A6 = B4) + (A2:A6 = B5) > 0, "No Shared IDs"))Row-Level Reconciliation Flag (Row A3)
Checks whether CRM ID A3 is posted in ERP range $B$2:$B$5.
=IF(ISBLANK(A3), "", IF(COUNTIF($B$2:$B$5, A3) > 0, "Matched in Both", "CRM Only"))Try the example
Use the original values or change them to test your understanding.