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.
kdschoolAsked:
Who is Participating?
 
als315Connect With a Mentor 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
resultCompare-lists.xlsx
0
 
Pawan KumarConnect With a Mentor 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
 
Steve KrauseConnect With a Mentor 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
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
kdschoolConnect With a Mentor 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
 
Pawan KumarConnect With a Mentor 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
 
als315Connect With a Mentor 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
 
Russell FoxConnect With a Mentor 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
 
Rob HensonConnect With a Mentor 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
 
kdschoolAuthor Commented:
All gave wonderful information but as a rooky the one I marked best was the easiest for me to figure out.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.