Compare Two Columns

What it does

You have two columns and want to know which values are in one but not the other. The macro highlights every cell whose value doesn't appear in the other column, in both directions, in light yellow. Comparison is case-insensitive and ignores leading and trailing spaces, so "ACME Corp" matches "acme corp ". Excel's VLOOKUP-with-IFERROR pattern works for this, but takes 4 clicks, a copy-down, and you have to remember to convert the result column to values before sharing the file. The macro just paints the diff and leaves the data alone.

Real-world example

A staff accountant at a 60-person SaaS company in Bangalore is closing the books for March. The accounts payable team sent her a list of 320 vendor invoices that should hit the GL this month. The GL export has 318 entries. She needs to know which two are missing and whether anything in the GL doesn't tie back to a real invoice.

Her usual method: a VLOOKUP from invoice number against the GL invoice column. She copies it down 320 rows, filters for #N/A, fixes the missing rows, then runs the same VLOOKUP from the other side. About 8 minutes plus the inevitable "is this filtering right" double-check.

She selects the invoice-number column on the AP sheet, Ctrl+clicks the invoice-number column on the GL sheet (both copied onto the same workbook tab), runs the macro. Yellow cells appear instantly: 2 in the AP list (the ones missing from the GL), 0 in the GL list. She knows exactly what to fix.

On a 320-row reconciliation, the macro highlights both directions in under one second. The bigger win is that there's no formula to clean up afterward and no helper column to delete. The file you share with the controller is just the cleaned data with the diff marked. Free, offline, no signup. Your data never leaves your machine.

How to use

First time? See How to run our tools for the 30-second setup.

1. Select your two columns to compare. Two adjacent columns work as a single drag selection. Two non-contiguous columns work if you select the first column, then Ctrl+click (Windows) or Cmd+click (Mac) the second.
2. Press Alt + F8 (Windows) or Option + F8 (Mac), pick CompareTwoColumns, click Run.
3. The macro highlights unmatched cells in both columns and reports the counts.
4. To clear the highlights: select the range and use Home > Clear > Clear Formats.

FAQ

Is the comparison case-sensitive?

No. "ACME" matches "acme" and "Acme". Leading and trailing whitespace are also ignored, so " Acme Corp " matches "Acme Corp". This is the most common reconciliation case. If you need a strict case-sensitive comparison, swap LCase(Trim(...)) for Trim(...) in the VBA source.

What if my columns have headers?

Include the header row in your selection if it makes selection easier; the macro will compare the headers too. Since the two header cells almost always differ ("Invoice ID" versus "GL Invoice"), they get highlighted along with any unmatched data. Just clear the highlights on the header row when you're done, or exclude headers from the selection up front.

Does it work across two different sheets?

Not in this version. Both columns must be on the active sheet. To compare across sheets, copy one of the columns to a temporary helper column on the other sheet, run the macro, then delete the helper column.

What if a cell value contains a pipe character?

The macro uses the pipe character internally to separate values when it builds its lookup string. If your data contains pipes, you may get false negatives in those rows. The macro is intended for typical reconciliation data (invoice IDs, names, SKUs) where pipes are rare. If your data routinely contains pipes, do a Find and Replace first.

Can I undo it?

Yes, Ctrl + Z (Windows) or Cmd + Z (Mac) right after running undoes the highlights. To clear later without undo: select the range and use Home > Clear > Clear Formats. The macro runs entirely offline.