I have two columns in Excel that I want to compare and find the differences between them.
Suppose
Col A has 50 numbers i.e 0511234567 Col B has 100 numbers in the same format
I have two columns in Excel that I want to compare and find the differences between them. Suppose Col A has 50 numbers i.e 0511234567 Col B has 100 numbers in the same format 

migrated from stackoverflow.com May 27 '11 at 20:05This question came from our site for professional and enthusiast programmers. 


Using Conditional FormattingHighlight column A. Click Conditional Formatting > Create New Rule > Use this formula to determine which cells to format > Enter the ff. formula:
Click the Format button and change the Font color to something you like. Repeat the same for column B, except use this formula and try another font color.
Using a Separate ColumnIn column C, enter the ff. formula into the first cell and then copy it down.
In column D, enter the ff. formula into the first cell and then copy it down.
Both of these should help you visualize which items are missing from the other column. 


Microsoft has an article detailing how to find duplicates in two columns. It can be changed easily enough to find unique items in each column. For example if you want Col C to show entries unique to Col A, and Col D to show entries unique to Col B:



See this SO question for your answer. 


If I understand well your question :
Replace True/false directives by a function or by a string like "Equal" or "different" 


It depends on the format of your cells and your functional requirements. With a leading "0" they could be formatted as text. Then you could use IF function to compare cells in Excel:
Example:
If they are formatted as numbers, you could subtract the first column from the other in order to get the difference:



With Excel 2007 and higher, you can use the builtin function to remove duplicates. Anyway, if you want to identify the duplicates, you can use array formula as it is explained here : http://chandoo.org/wp/2009/03/25/usingarrayformulasexample1/ Regards, Max 


This formula will directly compare two cells. If they are the same, it will print



Please refer this article. It has the solution for you http://support.microsoft.com/kb/213367 Further it is explained in my blog as well http://hrftools.blogspot.com/2012/10/howtocomparetwodatasetsusingexcel.html 

