ps.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

David wrote:

Dave Peterson wrote

Since you have 4 different lists, how about an alternative?

Create a new sheet (call it sheet2)
Put Name in A1
Copy the 4 lists into column A of this new sheet (one under the other)

Then select that range (A1:A###)
data|filter|advanced filter
Copy to another location
List range: (should be entered (a1:A###)
copy to: B1
Check Unique records only box

Now you have a list of unique names in column B.
Delete column A (we're done with it).

In B1, put: On List 1
In C1, put: On List 2
in D1, put: On List 3
in E1, put: On list 4

In B2, put this formula:
=isnumber(match(a2,sheet1!a:a,0))

In C2, put this:
=isnumber(match(a2,sheet1!b:b,0))

In D2:
=isnumber(match(a2,sheet1!c:c,0))

In E2:
=isnumber(match(a2,sheet1!D:D,0))

And drag down as far as column A extends.

Now select columns A:E and do Data|Filter|Autofilter.

You can filter to show the Falses in any column to see where its
missing.

Well, things broke when I tried to copy the 2nd list (and presumably
would do the same with 3rd and 4th) with a #REF to cells from that 2nd
list, which references cells from a 2nd external file. I didn't pursue
things any further. Remember these lists are "built" as a result of links
to external files. If I pasted values, wouldn't I have to repeat your
proposed process each time one or more lists change (quite often)?
Basically this is a test file to insure I have not missed adding/deleting
a name from all 4 files. I can immediately see if things don't match when
length of lists differ, but then have to scan visually to see what name
starts the difference. Additionally each source range is from a different
range in each of the source workbooks.

Here's what I want:
If name is in A, but not in B or C or D, I want to know
If name is in B, but not in A or C or D, I want to know
If name is in C, but not in A or B or D, I want to know
If name is in D, but not in A or B or C, I want to know

Am I asking for 4 conditions, thereby exceeding CF's limit of 3? If so,
I'll accept 3 out of 4 <g.

--
David

--

Dave Peterson