Conditional Formatting & Rules
Find Potential Duplicate Columns with TEXTJOIN and COUNTIF
Use simple column signatures to find possible duplicates in a small text or numeric table, then verify the original cells.
Why row-wise duplicate rules miss duplicated vertical columns—and how column signatures catch them
CSV exports and copy-pasted financial schedules occasionally repeat an entire metric column under two different headers (e.g., Col A "Net_Sales" and Col C "Gross_Sales" both holding 120 | 145 | 160). Because COUNTIF cannot compare multi-row vertical vectors directly, build a single delimiter-joined column signature row A5:D5 using =TEXTJOIN("|", FALSE, A2:A4) (passing ignore_empty = FALSE so blank positions remain distinct). These simple signatures can collide if cell values contain the separator, collapse distinctions such as numeric 1 versus text "1", and COUNTIF is case-insensitive and treats wildcard characters specially. Treat matches as candidates only: compare the original typed cells before deleting or merging columns.
Once each column has a canonical string fingerprint in row 5, =COUNTIF($A$5:$D$5, A5) > 1 highlights every duplicated column in Conditional Formatting, and summing a 0/1 flag row counts total colliding columns instantly.
| Matrix Column Vector (Rows 2–4) | TEXTJOIN("|", FALSE, Col) Signature | COUNTIF($A$5:$D$5, Sig) > 1 | Audit Status |
|---|---|---|---|
| Col A: [120, 145, 160] | "120|145|160" | TRUE (Count = 2, matches Col C) | Duplicate Column Flagged |
| Col B: [85, 92, 110] | "85|92|110" | FALSE (Count = 1) | Unique Column |
| Col C: [120, 145, 160] | "120|145|160" | TRUE (Count = 2, matches Col A) | Duplicate Column Flagged |
| Col D: [40, 55, 62] | "40|55|62" | FALSE (Count = 1) | Unique Column |
Flag a Potential Duplicate Signature
Returns TRUE when column A’s TEXTJOIN vertical signature in A5 appears more than once across A5:D5.
=COUNTIF(A5:D5, A5) > 1Count Columns with Potentially Matching Signatures
Sums per-column duplicate flags in A6:D6 (=IF(COUNTIF($A$5:$D$5, A5)>1, 1, 0)) to report how many columns participate in duplicate collisions.
=SUM(A6:D6)Try the example
Use the original values or change them to test your understanding.