Filter data using a formula
 

I've seen this question in an earlier post and was wondering if anyone could help as I can't make it work. I'm using Excel 97

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.

Filter data using a formula
 

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

ctrl+shift+enter, not just enter
copy down


"R G" wrote:

I've seen this question in an earlier post and was wondering if anyone could help as I can't make it work. I'm using Excel 97

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.

Filter data using a formula
 

Try this...

Using named ranges...

Name: referes to A$2:A$8
Group: refers to B$2:B$8

D1 = the group that you want to "filter" on = A

Enter this array formula** in D2:

=IF(ROWS(D$2:D2)<=COUNTIF(Group,D$1),INDEX(Name,SM ALL(IF(Group=D$1,ROW(Name)),ROWS(D$2:D2))-MIN(ROW(Name))+1),"")

Copy down to enough cells that it covers the maximum number for any group.
In your sample Group A appears the most times, 4, so you need to copy the
formula to *at least* 4 rows.

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

--
Biff
Microsoft Excel MVP


<R G wrote in message ...
I've seen this question in an earlier post and was wondering if anyone
could help as I can't make it work. I'm using Excel 97

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.