compare IP lists

[syd_p]

Hello,

Have ecel 2010 and I have two columns of IP addresses. 3 000 or so.
they are nearly the same - but I want to highlight the differences.
So if one IP exists in both lists that is good.
But if one exists in the left hand col but not the right I want to
identify this IP
And also if one IP exists in the right but not in the left I want to
identify that one too.
Any ideas?

-Syd

 

[Don Guillett Excel MVP]

Hello,

Have ecel 2010 and I have two columns of IP addresses. 3 000 or so.
they are nearly the same - but I want to highlight the differences.
So if one IP exists in both lists that is good.
But if one exists in the left hand col but not the right I want to
identify this IP
And also if one IP exists in the right but not in the left I want to
identify that one too.
Any ideas?

-Syd

A looping macro to compare and highlight or delete the cell. Or, I
don't like a lot of conditional formatting but it would also probably
work.

 

[Pete_UK]

Assume IP addresses in columns A and B. To test each column A entry to
see if it occurs anywhere in column B use this (eg in C1):

=IF(ISNA(MATCH(A1,B:B,0)),"absent","good")

then copy down. Similarly, to test each column B entry to see if it
occurs anywhere in column A, use this (eg in D1):

=IF(ISNA(MATCH(B1,A:A,0)),"absent","good")

and copy this down.

Hope this helps.

Pete

 

[syd_p]

Assume IP addresses in columns A and B. To test each column A entry to
see if it occurs anywhere in column B use this (eg in C1):

=IF(ISNA(MATCH(A1,B:B,0)),"absent","good")

then copy down. Similarly, to test each column B entry to see if it
occurs anywhere in column A, use this (eg in D1):

=IF(ISNA(MATCH(B1,A:A,0)),"absent","good")

and copy this down.

Hope this helps.

Pete

OK I am a bit further now.
Looking macro - thanks
use ISNA (google explains IS Not Available)
Not sure what is meant with copy down?
Probably I need to generate a 3 rd column - not found in Column1
and a 4 th column - not found in Column 2
Can I do that?
Sorry I am a newbie.
Please ignore if this is too simple..

-Syd

 

[zvkmpw]

Have ecel 2010 and I have two columns of IP addresses. 3 000 or so.

they are nearly the same - but I want to highlight the differences.
So if one IP exists in both lists that is good.
But if one exists in the left hand col but not the right I want to
identify this IP
And also if one IP exists in the right but not in the left I want to
identify that one too.

One way to start is to put this in C1:
=IF(OR(A1="",COUNTIF(B:B,A1)>0),"", A1&" missing from column B.
")&
IF(OR(B1="",COUNTIF(A:A,B1)>0),"", B1&" missing from column A. ")

Then select C1 and use
Edit > Copy

Then select the column C1 to 4000 (past the end of the data) and use
Edit > Paste

Then
- if columns A and B of a row are good then column C would be blank.
- if column A of a row is missing in B, then column C would identify
it.
- if column B of a row is missing in A, then column C would identify
it.
- if both A and B are missing, column C would identify both.

Modify to suit.