Help

[Steve]

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.

 

[Eva]

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)

 

[Steve]

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.

 

[T. Valko]

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$22)>C$2,"",INDEX(Rng1,SMALL(IF(ISNA(MATCH(SubRng1,Rng2,0)),ROW(SubRng1)),ROWS(D$22))))

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

 

[Steve]

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]

The changes that I made were instead of selecting

the entire column A, I only selected the range of interest.

Ok, that's where you went wrong!

We can reference only the specific range of interest but that'll require a
somewhat longer, more complicated formula.

Here's a small sample file that demonstrates this using the original formula
and the modified formula referencing only the specific range.

Steve.xls 18kb

http://cjoint.com/?bfr6kgYdZD

 

[Steve]

ok, apparently my initial response to you got lost in the network somewhere.
I've tried the formulas, and have both positive, and negative results.
First, the first function provided the correct number of missing names.
Second, the next function did not provide the correct name. I even double
checked to make sure I wasn't reading something incorrectly-- with an IF
equation.

I did make the necessary modifications to the equations to match my specific
worksheet configurations. I changed SubRng1 to the entire range-- I'm
guessing you only selected 5 rows for sampling purposes.
I then changed Rng1 from the entire column, to the specific range I needed.
Then, the same for Rng2.
The logic is fairly clear-- with the exception of your statement on arrays.
I've been using Sumproduct for over 3 years now, and hadn't heard the
keystroke arrangement you state before. For this reason, I'd appreciate some
further clarification on that.

Thus my two versions of your strings are as follows:
=SUMPRODUCT(--(ISNA(MATCH(APN!F$5:F$100,C$12:C$43,0))))
Where the APN!F$5:F$100 is my source data array
and
C$12:C$43 is my summary data array.

Then,

=IF(ROWS(H$12:H12)>G$12,"",INDEX(APN!F$5:F$100,SMALL(IF(ISNA(MATCH(APN!F$5:F$100,C$12:C$43,0)),ROW(APN!F$5:F$100)),ROWS(H$12:H12))))

Where h$12:h12, and G$12 are my equation placements/starting positions.
APN!F$5:F$100, and C$12:C$43 are as above- Source, and summary arrays,
respectively.

I'm willing to buy that I missed something. I did however get the incorrect
name.

 

[Steve]

Don't know if anyone can see this or not-- it appears the board isn't
accepting new posts..... anyone else having this issue?

 

[T. Valko]

I posted this earlier. I guess you didn't see it?

The changes that I made were instead of selecting
the entire column A, I only selected the range of interest.

Ok, that's where you went wrong!

We can reference only the specific range of interest but that'll require a
somewhat longer, more complicated formula.

Here's a small sample file that demonstrates this using the original formula
and the modified formula referencing only the specific range.

Steve.xls 18kb

http://cjoint.com/?bfr6kgYdZD

 

[David Biddulph]

No, we can't see this.
--
David Biddulph

Don't know if anyone can see this or not-- it appears the board isn't
accepting new posts..... anyone else having this issue?

....

 

[Steve]

smart alec.....
;-)

No, we can't see this.
--
David Biddulph


....


.

 

[Steve]

Hi T.
For some reason all of today's posts were blocked for 4 or 5 hours here....
I could see everything from last night, but nothing from today- until about
an hour ago.

Ok, got your point-- Rng1 MUST full column.
Also, I see that in setting the array functions-- with the {}, the cell has
to be active.
It's working now.
Then, I'm guessing that the formula 2 is the one that doesn't require the
full column array? It too works.

One more question--
Can I set this up with a macro/user form?
If so, what would I need to activate the array? Or would I just do it after
the equation is set in place?


Thank you.
Best.

Have a

 

[T. Valko]

Can I set this up with a macro/user form?

I'm sure you could but I'm not sure how you'd do that. My "forte" is
formulas!

If you're wanting to load those values into a user form you may do it a
completely different way that doesn't even use a formula (unless you want
both, the extracted list on the worksheet and then use that list to dump
into the user form).

I would suggest you post this to the programming forum.

 

[Steve]

Fair enough.
Thanks again for your help.

I'm sure you could but I'm not sure how you'd do that. My "forte" is
formulas!

If you're wanting to load those values into a user form you may do it a
completely different way that doesn't even use a formula (unless you want
both, the extracted list on the worksheet and then use that list to dump
into the user form).

I would suggest you post this to the programming forum.

--
Biff
Microsoft Excel MVP





.

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Fair enough.
Thanks again for your help.