Conditional filter using array formula

[matthew.clegg]

Summary:
I would like to enter an equation that will return the first field
(that is true for a certain condition) from a list. Then subsequent
rows will return the next field (again according to the condition).
I can't use filters or pivots for this spread sheet.

Example:
I have a list as per:
Name Group
Bob A
Fred B
Matt B
Eric A
Dave A
Stew B
Fred A

I want to return all names that are in group A (condition: Group = A):
Name
Bob
Eric
Dave
Fred

or all names that are in group B (condition: Group = B):
Name
Fred
Matt
Stew

My original list will be edited and will need to automatically
generate the required output lists. Once there are no more fields to
return (e.g. Fred in group A) then the formaula should return blank
cells.
Thanks in advance for any help/suggestions.
Matt

 

[Bob Phillips]

=IF(ISERROR(SMALL(IF(($B$1:$B$20="Group")+($B$1:$B$20="A"),ROW($A$1:$A$20),""),ROW($A1:$A$20))),"",
INDEX($A$1:$A$20,SMALL(IF(($B$1:$B$20="Group")+($B$1:$B$20="A"),ROW($A$1:$A$20),""),ROW($A1:$A$20))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Teethless mama]

=IF(ISERR(SMALL(IF(Group="A",ROW(INDIRECT("1:"&ROWS(Name)))),ROWS($1:1))),"",INDEX(Name,SMALL(IF(Group="A",ROW(INDIRECT("1:"&ROWS(Name)))),ROWS($1:1))))

ctrl+shift+enter, not just enter
copy down

 

[T. Valko]

Try this:

Name = A2:A8
Group = B2:B8

D1 = group lookup

Enter this array formula** in D2 and copy down to a number of cells that
will cover the max number of names in any group:

=IF(ROWS(D$22)<=COUNTIF(Group,D$1),INDEX(Name,SMALL(IF(Group=D$1,ROW(Name)-MIN(ROW(Name))+1),ROWS(D$22))),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[matthew.clegg]

Guys thanks very much for the help, unfortunately I can't get any of
them to work. It may be due to two reasons:
1)My data is a sheet called Jobs, and my formula will be in a sheet
called Personal
2)In trying to create a generic example I may have inadvertently
complicated the query: Instead of Group the field is titled P/W (e1)
and the data is in range e2:e50 (range named PW), and instead of the
entries being A or B it is p or w. The other difference is that it
isn't names it is numbers and the fields are titled Record (c1) and in
range c2:c50 (range named Record).
I tried to adapt it to my data set but haven't been able to crack it.
Any chance you can help resolve this.
Cheers
Matt

 

[T. Valko]

All of the suggested formulas do what you asked for. I favor my own for a
couple of reasons but that's beside the point.

The clarified explanation you provided makes no difference, the formulas
should work.

However, since the data to be returned is numeric we can use a slightly
simpler array formula** to get the results.

Assume G1 = P or W

Enter this array formula** in G2 and copy down until you get blanks:

=IF(ROWS(G$2:G2)<=COUNTIF(PW,G$1),SMALL(IF(PW=G$1,Record),ROWS(G$2:G2)),"")

This assumes that for every P or W there is a corresponding number.

This will return the numeric values sorted in ascending order.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Make sure you enter the formula as an array. I'm wondering if that may be
what the problem is with the other formulas.

 

[matthew.clegg]

All of the suggested formulas do what you asked for. I favor my own for a
couple of reasons but that's beside the point.

The clarified explanation you provided makes no difference, the formulas
should work.

However, since the data to be returned is numeric we can use a slightly
simpler array formula** to get the results.

Assume G1 = P or W

Enter this array formula** in G2 and copy down until you get blanks:

=IF(ROWS(G$2:G2)<=COUNTIF(PW,G$1),SMALL(IF(PW=G$1,Record),ROWS(G$2:G2)),"")

This assumes that for every P or W there is a corresponding number.

This will return the numeric values sorted in ascending order.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Make sure you enter the formula as an array. I'm wondering if that may be
what the problem is with the other formulas.

--
Biff
Microsoft Excel MVP






- Show quoted text -

Ok, that was excellent.
I went for the last one as that was the one I worked out first. In my
spreadsheet this looks like this:
=IF(ROWS(A$3:A3)<=COUNTIF(PW,J$2),SMALL(IF(PW=J$2,Record),ROWS(A
$3:A3)),"") - array entered
where PW is my named range, J$2 can be toggled to eiter p or w and
Record is the other named range that the formula filters and returns.

What would be awesome is if I could add two things to this.
1) In the original data I have another range named "status". Could
the
formula be ammended to only return records that have values not equal
to "completed" as their status?
2) Another column in the data is priority (range named priortiy), and
this contains values like a, b, c or blank to inidcate priority. Is
it
possible that the formula now returns the same data but orders it by
priortity, and if no priorty then orders from 1st to last as it
currently does?
Thank you in advance for educating me in the ways of array formulas.
Cheers
Matt