creating a list with criteria

[Patrick]

I want the third column to find all the people in the
list who have "0" next to their name, and return a list,
like so...

Total Flown Crewmember have done 0
1 Allen, Amenta
0 Amenta, Bilton
1 Apostolides, Bounds
8 Baker,
1 Belin,
0 Bilton,
3 Bogetto,
0 Bounds,

 

[Peo Sjoblom]

You could use either autofilter or advanced filter and filter on total flown
and zero
Debra Dalgleish has some filter examples on her website

http://www.contextures.com/tiptech.html



--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Biff]

Hi!

Here's a formula version:

Assume A1:C1 are headers

In C2 enter this formula as an array - CTRL,SHIFT,ENTER
and copy down until you get #NUM! errors:

=INDEX(B$2:B$9,SMALL(IF(A$2:A$9=0,ROW(INDIRECT
("A$1:A$"&COUNT(A$2:A$9)))),ROW(1:1)))

Biff

 

[Patrick]

Nope. Doesn't work. Not sure if I'm doing the
CTRL,SHIFT,ENTER thing incorrectly or what, but when I
copy the formula into C2, it doesn't return a name with a
zero next to it.

 

[Domenic]

I just tried the formula and it works fine. Make sure that when you
enter your formula you confirm it by pressing the CONTROL and SHIFT keys
together, and hitting ENTER while those keys are pressed. If you've
entered it correctly, Excel will add braces around the formula, like
this...

{=INDEX(B$2:B$9,SMALL(IF(A$2:A$9=0,ROW(INDIRECT("A$1:A$"&COUNT(A$2:A$9)))
),ROW(1:1)))}

Hope this helps!

 

[Guest]

Great. I was doing it incorrectly. Now, what exactly are
the brackets doing?

 

[Gord Dibben]

"What are the brackets doing?"

See Chip Pearson's website for info on Array Formulas.

http://www.cpearson.com/excel/array.htm

Gord Dibben Excel MVP