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

On Nov 19, 2:59*pm, syd_p <sydneypue...@yahoo.com> wrote:
> 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.

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

On Nov 19, 8:59*pm, syd_p <sydneypue...@yahoo.com> wrote:
> 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

On 20 Nov, 03:29, Pete_UK <pashu...@auditel.net> wrote:
> 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
>
> On Nov 19, 8:59*pm, syd_p <sydneypue...@yahoo.com> wrote:
>
> > Hello,
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


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

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