How do I compare two columns contents for differences

[Denny]

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?

 

[ScottO]

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?

 

[Marsha M]

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

 

[Paul]

You could just use Conditional Formatting for this, rather than a whol
column of formulas.

Excel 2007 instructions:
Select A1:A194 and click Conditional Formatting -> New Rule...
Select 'Use a formula....'
Type the following formula: =ISERROR(MATCH(A1,$B$1:$B$150,0))
Select a format (fill/font color, etc.)
Click OK a few times to return to the sheet.

Any values in column A that don't appear in column B will now b
highlighted.

 

[Armor]

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

 

[Luke M]

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)