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.

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

share|improve this question

migrated from stackoverflow.com May 27 '11 at 20:05

This question came from our site for professional and enthusiast programmers.

    
I think this can be done with Excel's built in functions and formulas. Seems to me off topic. –  Matt Handy May 27 '11 at 7:19
    
can you please specify how to do that? –  Anonymous May 27 '11 at 7:20
    
So do you want to know which numbers are in Col A only and which numbers are in Col B only? –  Tom Shaw May 27 '11 at 7:22
    
No, first i want to know all those numbers which are not in Col A but in Col B and then i want to know all those numbers which are in Col A but not in Col B. –  Anonymous May 27 '11 at 7:54
    
I have used this formula :- =COUNTIF($A:$A,$B:$B)=0 but i'm just getting those numbers which are in col B and not in col A. –  Anonymous May 27 '11 at 7:58
show 1 more comment

7 Answers 7

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.

share|improve this answer
add comment

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:

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

See this SO question for your answer.

share|improve this answer
add comment

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"

share|improve this answer
add comment

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

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

share|improve this answer
add comment

This formula will directly compare two cells. If they are the same, it will print True, if one difference exists, it will print False. This formula will not print what the differences are.

=IF(A1=B1,"True","False")
share|improve this answer
1  
Guess you've been downvoted because =A1=B1 will achieve the same result as your (more complex) formula and/or that does not address "find the differences". However, please do not be disheartened! I am still struggling with some of the 'administrative' aspects of this site and be assured that constructive efforts are appreciated even when not providing the 'best' solution (highly subjective anyway). –  pnuts Oct 11 '12 at 1:39
add comment

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.