Conditional filter using array formula
 

On May 28, 12:22*pm, "T. Valko" wrote:
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

wrote in message

...



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- Hide quoted text -

- 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