Finding differences between two tables

Who can help me?

I have 2 tables with mainly the same names. I want to find the names that
are not in both list.

I managed to do it the following way, but I think that it can be done
easier.

I did:
Advances filter: as criterium range one of the 2 tables.
With Edit - Go To - I highlighted the visible cells and coloured them.
Removed the filter
With Edit - Find - Format - I highlighted the noncoloured cells.

Thanks Anne

Here are two ways you can do that but I wouldn't say they are much easier
than what you did. Put both lists on the same sheet in the same column, one
under the other, say in column A.
Data is in column A starting at A1.

Formula in B1 is =If(Countif(A:A,A1)=1,True,False) Drag this to bottom of

table. Values appearing more than once appear as false. This can be modified

easily to show dups, triplicates etc.



Also:

Format - Conditional Formatting

Formula is:

=COUNTIF(A:A,A1)>1

Set the format as you wish.



HTH Otto


"Anne" <aaschouten@hotmail.nl> wrote in message
news:3f007393$0$76764$1b62eedf@news.wanadoo.nl...
> Who can help me?
>
> I have 2 tables with mainly the same names. I want to find the names that
> are not in both list.
>
> I managed to do it the following way, but I think that it can be done
> easier.
>
> I did:
> Advances filter: as criterium range one of the 2 tables.
> With Edit - Go To - I highlighted the visible cells and coloured them.
> Removed the filter
> With Edit - Find - Format - I highlighted the noncoloured cells.
>
> Thanks Anne
>
>

Thanks Otto, these are useful alternatives.

Anne


"Otto Moehrbach" <ottomnospam@worldnet.att.net> wrote in message
news:exZN8EAQDHA.1072@TK2MSFTNGP10.phx.gbl...
> Here are two ways you can do that but I wouldn't say they are much easier
> than what you did. Put both lists on the same sheet in the same column,
one
> under the other, say in column A.
> Data is in column A starting at A1.
>
> Formula in B1 is =If(Countif(A:A,A1)=1,True,False) Drag this to bottom of
>
> table. Values appearing more than once appear as false. This can be
modified
>
> easily to show dups, triplicates etc.
>
>
>
> Also:
>
> Format - Conditional Formatting
>
> Formula is:
>
> =COUNTIF(A:A,A1)>1
>
> Set the format as you wish.
>
>
>
> HTH Otto
>
>
> "Anne" <aaschouten@hotmail.nl> wrote in message
> news:3f007393$0$76764$1b62eedf@news.wanadoo.nl...
> > Who can help me?
> >
> > I have 2 tables with mainly the same names. I want to find the names
that
> > are not in both list.
> >
> > I managed to do it the following way, but I think that it can be done
> > easier.
> >
> > I did:
> > Advances filter: as criterium range one of the 2 tables.
> > With Edit - Go To - I highlighted the visible cells and coloured them.
> > Removed the filter
> > With Edit - Find - Format - I highlighted the noncoloured cells.
> >
> > Thanks Anne
> >
> >
>
>

How about . . .

Sort both Tables by Name order (Important!)

Take first Table
Complete a new Column with this formula (assumes Name is in Col A)
If(vlookup(a1,Range_in second_table,1)=a1,"Matched","Not in Table 2")

Take the second Table
Complete a new Column with this formula (assumes Name is in Col A)
If(vlookup(a1,Range_in first_table,1)=a1,"Matched","Not in Table 1")

It may not report perfect matches for first names in the lists
so if your still with me
add a false name "aaaa" as to each Table before sorting. This ensure that
the first items will return "Matched"

M.

"Anne" <aaschouten@hotmail.nl> wrote in message
news:3f007393$0$76764$1b62eedf@news.wanadoo.nl...
> Who can help me?
>
> I have 2 tables with mainly the same names. I want to find the names that
> are not in both list.
>
> I managed to do it the following way, but I think that it can be done
> easier.
>
> I did:
> Advances filter: as criterium range one of the 2 tables.
> With Edit - Go To - I highlighted the visible cells and coloured them.
> Removed the filter
> With Edit - Find - Format - I highlighted the noncoloured cells.
>
> Thanks Anne
>
>

Thanks Michael,
I think this is a very good solution.
Anne

"Michael Goodwin" <goodwinm@oceanfree.net> wrote in message
news:%230tWGCbQDHA.3088@TK2MSFTNGP10.phx.gbl...
> How about . . .
>
> Sort both Tables by Name order (Important!)
>
> Take first Table
> Complete a new Column with this formula (assumes Name is in Col A)
> If(vlookup(a1,Range_in second_table,1)=a1,"Matched","Not in Table 2")
>
> Take the second Table
> Complete a new Column with this formula (assumes Name is in Col A)
> If(vlookup(a1,Range_in first_table,1)=a1,"Matched","Not in Table 1")
>
> It may not report perfect matches for first names in the lists
> so if your still with me
> add a false name "aaaa" as to each Table before sorting. This ensure that
> the first items will return "Matched"
>
> M.
>
> "Anne" <aaschouten@hotmail.nl> wrote in message
> news:3f007393$0$76764$1b62eedf@news.wanadoo.nl...
> > Who can help me?
> >
> > I have 2 tables with mainly the same names. I want to find the names
that
> > are not in both list.
> >
> > I managed to do it the following way, but I think that it can be done
> > easier.
> >
> > I did:
> > Advances filter: as criterium range one of the 2 tables.
> > With Edit - Go To - I highlighted the visible cells and coloured them.
> > Removed the filter
> > With Edit - Find - Format - I highlighted the noncoloured cells.
> >
> > Thanks Anne
> >
> >
>
>