How to compare two columns and find differences in excel

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
Answer:

Using Conditional Formatting Highlight column A. Click Conditional Formatting > Create New Rule > Use this formula to determine which cells to format > Enter the ff. formula: =countif($B:$B, $A1) 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. =countif($A:$A, $B1) Using a Separate Column In column C, enter the ff. formula into the first cell and then copy it down. =if(countif($B:$B, $A1)<>0, "-", "Not in B") In column D, enter the ff. formula into the first cell and then copy it down. =if(countif($A:$A, $B1)<>0, "-", "Not in A") Both of these should help you visualize which items are missing from the other column.

Was this solution helpful to you?

Other answers

See http://stackoverflow.com/questions/5051484/how-do-i-compare-two-columns-in-excel for your answer.

iDevlop

Microsoft has http://support.microsoft.com/kb/213367 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: A B C D 1 3 =IF(ISERROR(MATCH(A1,$B$1:$B$5,0)),A1,"") =IF(ISERROR(MATCH(B1,$A$1:$A$5,0)),B1,"") 2 5 (fill down) (fill down) 3 8 .. .. 4 2 .. .. 5 0 .. ..

Tom Shaw

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: =IF ( logical_test, value_if_true, value_if_false ) Example: =IF ( A1<>A2, "not equal", "equal" ) If they are formatted as numbers, you could subtract the first column from the other in order to get the difference: =A1-A2

Matt Handy

If I understand well your question : =if(Ax = Bx; True_directive ; False_directive) Replace True/false directives by a function or by a string like "Equal" or "different"

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/using-array-formulas-example1/ Regards, Max

JMax

Related Q & A:

How To Compare Grocery Prices Online?Best solution by Yahoo! Answers
How I can find string in excel with vba?Best solution by Stack Overflow
How to decrypt multiple columns?Best solution by Stack Overflow
How do I compare two tables with the same column definitions in different schemas?Best solution by Database Administrators
How to make collapsible columns for a table?Best solution by Stack Overflow

Just Added Q & A:

How many active mobile subscribers are there in China?Best solution by Quora
How to find the right vacation?Best solution by bookit.com
How To Make Your Own Primer?Best solution by thekrazycouponlady.com
How do you get the domain & range?Best solution by ChaCha
How do you open pop up blockers?Best solution by Yahoo! Answers

For every problem there is a solution! Proved by Solucija.

Got an issue and looking for advice?
Ask Solucija to search every corner of the Web for help.
Get workable solutions and helpful tips in a moment.

Just ask Solucija about an issue you face and immediately get a list of ready solutions, answers and tips from other Internet users. We always provide the most suitable and complete answer to your question at the top, along with a few good alternatives below.