Morning T.
Thank you for your response.
Ok, I tried your formulas and I do indeed get the correct number of missing
names. I then tried the second formula, and get the wrong name.
I double checked to make sure, and did an IF test to verify.
At this point I'm willing to buy that I'm doing something wrong.

The changes that I made were instead of selecting the entire column A, I
only selected the range of interest.
Also, I'm not entirely clear on your explanation of arrays. I get the
key-stroke selection part, but not the HAVE TO part.
I know-- consider me half blind... well, make that insatiably curious.



"T. Valko" wrote:

Try this...

In the formulas:

Rng1 refers to Source sheet A:A
SubRng1 refers to Source sheet A$2:A$7
Rng2 refers to Summary sheet A$2:A$5

On the Summary sheet enter this formula in C2. This will return the count of
missing names.

=SUMPRODUCT(--(ISNA(MATCH(SubRng1,Rng2,0))))

On the Summary sheet enter this array formula** in D2:

=IF(ROWS(D$2:D2)C$2,"",INDEX(Rng1,SMALL(IF(ISNA(M ATCH(SubRng1,Rng2,0)),ROW(SubRng1)),ROWS(D$2:D2))) )

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Copy the formula in D2 down until you get blanks.

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
Hi Eva,
Thank you for your response.
With my initial check of this function, I see that it will leave the cell
blank if the name exists on my source sheet. However, if the cell for my
criteria is blank, or if the value is not on the source sheet, it returns
a
#N/A error.
I then reversed the placement from my sum sheet to my source sheet, as
well
as placed a name in a cell that does not exist on my sum sheet.
I made the necessary sheet name modifications, and reduced the A:A to the
range/column I needed as well.

Ok, I got a response, but it's reversed from my desired goal. I.e., I put
the function on my source sheet- my desire/need is for it to be on the
summary sheet. For the location that has the new name, it returned a #N/A
error.
While this would work-- it needs to be "dummy proof" in that someone who's
never seen this kind of thing before needs to see a name, and not an error
note.

My experience with VLookup is real limited, so as I understand it, it's
looking for a specific element, throughout an array, within a specified
column.

E.g.

SourceSht SumSht
John Sam
Dave Dave
Betty Joe
Don John
Joe
Sam

I need to show on the SumSht that I missed putting in Betty- in one cell,
and Don, in another cell.

I hope this helps make it clearer.
Again-- thank you.


"Eva" wrote:

Can you instert the column on your source sheet and use vlookup function
(for
example
if(isna(vlookup(A1,SummarySheetA:A,1,false))=false ,"",vlookup(A1,SummarySheetA:A,1,false)
assuming that your lookup data are in column A in source and lookup value
are
also in col A (change as you wish) and then filter all out to see which
ones
are new? (all non blank or all with error messages)
--
Click yes if helped.

Greatly appreciated
Eva


"Steve" wrote:

Howdee all.
I was just presented with a problem that has got me curious.
While it's something I have thought about before, I never knew where to
begin with it.
I have two data sheets. Each have names, values, etc...-- one is a
source
sheet, and the second is a summary sheet of the source data.
On the Summary sheet, I have a bunch of sumproduct functions with two
criteria arrays, and one sum array. At times I'm short a name or two,
and
must manually work through my source sheet to identify a name that's
missing
on my Summary sheet (As you might imagine, it's a pain-staking process
for
longer data-sets).

What I want to do is to set up a function that will look at all the
names on
the source sheet, and compare them to the summary sheet names. If a
name is
missing on my summary sheet, I want to have it tell me the name that's
missing.

I've used Match(), if(), and maybe one or two others (that I can't
remember
at the moment) to try this, and Match just tells me if the name doesn't
exist-- #N/A error.

Does anyone have an idea on either a single worksheet function, or a
combination of nested worksheet functions to accomplish this?

I don't care where the name is on my source sheet-- just if I've missed
having it on my summary sheet-- and what the name actually is.

Thank you for your helps-- in advance.
Best.



.