Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excell Data Filter Fails to Integrate New Data-rows | Excel Discussion (Misc queries) | |||
A formula to filter data and copy it from one worksheet to another | Excel Worksheet Functions | |||
Can I use a formula to filter data? | Excel Worksheet Functions | |||
in data/filter/auto filter | Excel Worksheet Functions | |||
"Criteria Range" in the "Data/Filter/Advanced Filter" to select Du | Excel Worksheet Functions |