Conditional Formatting & Rules
Highlight & Count Duplicate Columns in a Matrix (TEXTJOIN Signature + COUNTIF)
Detect accidentally duplicated import columns across a multi-row table by serializing each column vector with TEXTJOIN("|", FALSE, A2:A4) and flagging signatures with COUNTIF($A$5:$D$5, A5) > 1.
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)!
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 |
Per-Column Duplicate Detection Rule = COUNTIF($A$5:$D$5, A5) > 1
Returns TRUE when column A’s TEXTJOIN vertical signature in A5 appears more than once across A5:D5.
=COUNTIF(A5:D5, A5) > 1Total Count of Colliding Columns Across the Matrix
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.