up vote 1 down vote favorite
1
share [g+] share [fb]

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)?

link|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. – Randolph West 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. – Randolph West 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. – Randolph West Jan 18 '11 at 23:20
feedback

3 Answers

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.

link|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 :-). – Randolph West Jan 18 '11 at 23:20
Thanks alot, it's exactly what I needed! – Anne Jan 18 '11 at 23:23
feedback
up vote 1 down vote

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.

link|improve this answer
feedback
up vote 0 down vote

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.

link|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

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