Array formula returning terminated employees

[malik641]

I have 2 sheets: Data and Terminated Employees, respectively.

IN DATA SHEET:
_Column_A_-----------------------------------------_Column_E_
A1:Employees (heading)---------------------------E1OT (date of
termination)
A2 and on: actual names of employees-------------E2 and on: dates

In column E, if the employee was NOT terminated, then the cell is left
blank. Otherwise, it has the date of their termination.

WHAT I'M LOOKING FOR:
In the Termination sheet, column B, I want a formula to return the
names of the employees who were terminated.

I get this far:
IF(Data!$E$2:$E$500<>\"\",Data!$A$2:$A$500,.....
and I can't think of what to do for the FALSE value of the IF
statement. I did try this:

{IF(Data!$E$2:$E$500<>\"\",Data!$A$2:$A$500,INDEX(Data!$A$2:$A$500,ROW(INDIRECT(\"Data!$A$2:$A$500\",ROWS($B$32:$B32)))))}
Copied from B32 on down.

Which if the value is false (i.e. the employee is not terminated), then
it will return the next row. The problem is the ARRAY itself will not
skip that row with the FALSE value. This ends up giving me 2 of the
same values (2 of the same employees, when there should only be one of
them).

Any suggestions?

 

[Biff]

Hi!

Try this:

Entered as an array using the key combo of CRL,SHIFT,ENTER:

=IF(ISERROR(SMALL(IF(Data!E$2:E$500="",ROW($1:$499)),ROW(1:1))),"",INDEX(Data!A$2:A$500,SMALL(IF(Data!E$2:E$500="",ROW($1:$14)),ROW(1:1))))

Copy down until you get blank cells meaning the data has been exhausted.

Biff

 

[Biff]

Ooops!

Hold on there a minute!

That formula will return all employees who HAVE NOT been terminated.

Use this formula to return all employees who HAVE been terminated:

=IF(ISERROR(SMALL(IF(Data!E$2:E$500<>"",ROW($1:$499)),ROW(1:1))),"",INDEX(Data!A$2:A$500,SMALL(IF(Data!E$2:E$500<>"",ROW($1:$14)),ROW(1:1))))

Biff

 

[malik641]

Hey Biff,
Thanks for the response. For some reason I couldn't get your formula to
work for me the way you have it set up. I ended using an older formula I
had in another worksheet. It is a combination of two formulas.

First (not an array):
=SUMPRODUCT(--(Data!$E$2:$E$500<>0),--(Data!$E$2:$E$500<>""))

And then I would use the second formula based on the first.
Second (array formula):
{=IF(ROWS(B$32:B32)<=$I$32,INDEX(Data!$A$2:$A$500,SMALL(IF((Data!$E$2:$E$500<>0)*(Data!$E$2:$E$500<>""),ROW(Data!$E$2:$E$500)-ROW(Data!$E$2)+1),ROWS(B$32:B32))),"")}

I couldn't understand (in your formula) what the ROW($1:$14) was used
for. Unless you were displaying the smallest 14 values...??? But oh
well, I got it to work anyway

Thanks again for the response Biff

 

[Biff]

Hi!

For some reason I couldn't get your formula to work for me the way you have
it set up.

I couldn't understand (in your formula) what the ROW($1:$14) was used for.

Yeah, that's my fault!

Usually when I reply to a post I test the formulas and then I copy/paste
them into the reply. Most of the time I use different range sizes and cell
references and then I edit them to meet the description of the posted
question. The ROW($1:$14) was the size of my test range and I missed editing
that after I posted my reply.

The corrected formula should be:

=IF(ISERROR(SMALL(IF(Data!E$2:E$500<>"",ROW($1:$499)),ROW(1:1))),"",INDEX(Data!A$2:A$500,SMALL(IF(Data!E$2:E$500<>"",ROW($1:$499)),ROW(1:1))))

ROW($1:$499) refers to the number of entries (size of the range) being used.
The actual physical location may be A2:A500 but the actual physical size of
that range is 499 elements. You could also think of it in terms of being the
virtual range. The virtual range will always start with position 1. Say for
example the physical range location is A101:A500. The virtual range would
still be 1:499.

It's that number that is used by the INDEX function to return the desired
value.

I can send you a sample file that demonstrates this, if you'd like.

Biff