Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

If I have two columns of data with 10000 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)?

share|improve this question
    
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

3 Answers 3

up vote 1 down vote accepted

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.

share|improve this answer
    
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

Adding to Linker3000's answer above.

I would sort on Column C so that the different cells would sort to the top. That's easier for me than having to scroll through 10,000 rows to see which ones are different.

share|improve this answer

Go to the Data Menu or Data Ribbon and select Filter.

This will create filters for each column that you can select in the top row. Deselect the values that you don't want to see, and it will leave the rows (with numbers) that you do want to see.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.