# How can I pairwise compare columns in Excel?

If I have two columns of data with 10,000 cells in each, and only three of the rows are not identical in both columns, how can I locate the different ones (without comparing each pair of cells)?

-
Must it be in Excel? I suppose you could write a macro to populate each list into an array, and loop through each with the contents of the other array respectively. But VLOOKUPs on both columns makes more sense to me. – user3463 Jan 18 '11 at 22:54
@Randolph Potter: I think the OP seeks to solve the problem within the excel application context, these types of requirements are common for scientific work. What is VLOOKUP? – Maxim Veksler Jan 18 '11 at 23:00
@Maxim - you don't know what VLOOKUP is? It's a very nice feature inside Excel which does Vertical Lookups in named regions of a spreadsheet. It compares with HLOOKUP, which does the same horizontally. I would suggest investigating it before suggesting that my answer was not within the Excel application context. No apology necessary. – user3463 Jan 18 '11 at 23:18
Thinking more on this, I feel that my original suggestion of a macro is more appropriate, given the vagueness of the question. – user3463 Jan 18 '11 at 23:20

Not sure you mean by "without comparing each pair of cells" as you have to do some work to get the result you want??

If the columns to compare are A and B, I would put the following in cell C1 and then copy it down the entire range:

=IF(A1=B1,"",1)

Then the rows that are different will have a 1 in the relevant C column.

-
The problem with this solution is that it assumes the data is sorted, and that only one column is being matched. It would work better with a VLOOKUP on both columns, where you can find matches between both sets, without needing to sort them. Of course my assumption is that the data is unique. VLOOKUP doesn't like duplicate data :-). – user3463 Jan 18 '11 at 23:20
Thanks alot, it's exactly what I needed! – Anne Jan 18 '11 at 23:23