excel 2010 compare two columns easily

Is there an easy way to put two columns on a page that should both have the same information and figure out what value is missing that is in one column that is not in the other column. The value could be missing from either column.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
You can use like this


if equal it will TRUE
if it not equal then it wil return FALSE

once the formula is defined , drag on the entire column.
Steve KrauseFront End DeveloperCommented:
Not sure if this is what you're looking for but, I have used this in the past;
Put this formula in Column C


So then you would see the Word "Match" in C if they were the same.

Obviously, or maybe not, this formula if for row 48, Change it to your row number and then copy the cell to other cells in column C and it will change the row number for you.
You can use conditional formatting to show missing values. For example data are:
List1      List2
A               H
B               G
C               F
D               D
E               N
F               A
G               B
H               X
Select first column, and add conditional formatting for it (use formula):
=COUNTIF($B$2:$B$9, $A2)=0
Select fill color for missing values
Correct max row to real
Same for second column:
=COUNTIF($A$2:$A$9, $B2)=0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

kdschoolAuthor Commented:
The columns do not equal each other there may be a few values missing in one that are not in the other. The rows don't match up . So if I use the conditional formatting that would work?
Pawan KumarDatabase ExpertCommented:
First sort both the columns then use.


if equal it will TRUE
if it not equal then it wil return FALSE

once the formula is defined , drag on the entire column.
@kdschool: you can see differences in both columns. Look at sample: you may not sort columns and matching values may be in dirrefent rows
Russell FoxDatabase DeveloperCommented:
You can also use VLOOKUP:
This says "try to find the value that's in cell A1 somewhere in the range B1 to B3, and return the value in column B (the "first" column in our one-column range) but only if it's an exact match (range lookup = "false").
Rob HensonFinance AnalystCommented:
Assuming your values are in columns A and B you can then use these formulas in separate columns, lets say D and E.

Column D:  =COUNTIF($B:$B,$A1)   copy down for the extent of values in column A

Column E:  =COUNTIF($A:$A,$B1)   copy down for the extent of values in column B

This will count the occurrences of each entry in the other column. The results which are zero shows that value does not occur in the other column.

If you apply an AutoFilter to columns D & E you can then filter on each column separately to show only those values where the result is zero. You will then be able to copy the values from column A or B and the copy will only take the values that are visible.

kdschoolAuthor Commented:
All gave wonderful information but as a rooky the one I marked best was the easiest for me to figure out.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.