Producing an automated list from a larger list

[Guest]

Hi all,

I have asked this before but did not get a suitable answer
Below is a crude "sample" of what i want to do.
Ultimately i will only be viewing or printing the outcome and just wanted to
know if it can be automated or not. I didnt want to use filter or autofilter
options under the data tab.

I have the table below, in a worksheet named " Attendance" which has 5
employees, Alf to Eric.
Each Person has a works No. 1-5
Column C shows whether they are in work or not (Y=In)

Works No Name In
1 Alf Y
2 Bob
3 Chris Y
4 Dave
5 Eric Y

I wish to extract the information from this table into a list (preferably
onto another worksheet) which removes those that are not in and shows just
those that are in.

I do not want rows that are blank. I want them shuffled up so that they are
in consecutive rows, see below

Works No Name
1 A
3 C
5 E

Can anyone help with the formula?

Chuckee

 

[Guest]

To pull Works #
=INDEX($A$2:$A$6,SMALL(IF($C$2:$C$6="Y",ROW($C$2:$C$6)-MIN($C$2:$C$6)-1),ROW(1:1)))
array entered (ctrl + shift + enter)

Copy this formula al the way down until you see a N/a# or alternatively
enclose it it with an if (Iserror (Index...) to avoid n/a# etc

Just change $A$2:$a$6 piece of the formula to to B$2:B$6 to pull names etc

 

[Guest]

Thanks, N Harkawat, this works but one question.

When the item is moved to the bottom of the list because it does not satisfy
the criteria (i.e the person is not in) it produces an error of #NUM!. Can
this error be shown as a blank cell instead
Chuckee