Finding differences between two tables

[Anne]

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

 

[Otto Moehrbach]

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]

Thanks Otto, these are useful alternatives.

Anne

 

[Michael Goodwin]

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]

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