How do I compare two columns contents for differences

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have two different columns which should contain the same contents but there
are diffences. Each column should have 194 entries (cells) but one has only
150. Is there a function in excel which would allow me to compare the
contents in each and highlight the 44 discrepancies?
 
One way ...
If the 194 cells are in ColA and the 150 cells are in ColB, then at
C1 put the formula
=MATCH(A1,$B$1:$B$150,0)
and copy down to C194.
The cells in ColC that return #NA indicate the missing entries from
ColB.
Rgds,
ScottO


| I have two different columns which should contain the same contents
but there
| are diffences. Each column should have 194 entries (cells) but one
has only
| 150. Is there a function in excel which would allow me to compare
the
| contents in each and highlight the 44 discrepancies?
 
I have used this formula in the past and have been able to then sort on
column C and it would but the number (matches) at the top followed by the
#N/As. Is there a way to eliminate the "sort on" criteria or do this in
another way?

thank you,
Marsha M
 
I am trying to use this example but, I am getting erroneous answers. It is
probably I am not understanding the output.

I have a column of IP addresses in the 4 octet format (xxx.xxx.xxx.xxx) .
There are 46 data entries.
I have been asked to check this list against my known IP addresses to see if
any of these IP addresses belong to me.

So, in Col. A I have the 46 IP addresses from the Domain Admins. Col B. I
have my 26 known IP addresses that belong to me.

I tried the formula and conditional formatting so that anything in Col B.
that was in Col. A would be green. But the formula is not reporting IPs in
Col B. that are in Col A.

I used this formula =ISERROR(MATCH(B1,$A$1:$A$46,0))

Where am I goofing up?

Thank you
 
Your current formula will highlight values that in in column B but NOT in
column A. Modify to:

=ISNUMBER(MATCH(B1,$A$1:$A$46,0))

If you're still having trouble, make sure all of your data is the same
(either all numbers formatted to look like an IP, or all text)
 
Back
Top