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

x
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.

Database ExpertCommented:
You can use like this

=A1=B1

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

once the formula is defined , drag on the entire column.
0
Front 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

=IF(A48=B48,"MATCH","DIFFERENT")

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.
0
Commented:
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
Compare-lists.xlsx
0

Experts Exchange Solution brought to you by

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

Author 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?
0
Database ExpertCommented:
First sort both the columns then use.

=A1=B1

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

once the formula is defined , drag on the entire column.
0
Commented:
@kdschool: you can see differences in both columns. Look at sample: you may not sort columns and matching values may be in dirrefent rows
0
Database DeveloperCommented:
You can also use VLOOKUP:
=VLOOKUP(A1,\$B\$1:\$B\$3,1,FALSE)
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").
0
Finance 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.

Thanks
Rob
0
Author Commented:
All gave wonderful information but as a rooky the one I marked best was the easiest for me to figure out.
0