Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple selection criteria
I have a worksheet with employee name, whether they are certified and their
function in separate columns. I want to use a formula to select only those employees who are certified and perform the audit function and put the results in a separate column. Is there a simple way to perform this operation? I seem to remember seeing an article relative to this but can't put my hands on it now. Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple selection criteria
What about trying Filter Auto FIlter
"Bigkahuna" wrote: I have a worksheet with employee name, whether they are certified and their function in separate columns. I want to use a formula to select only those employees who are certified and perform the audit function and put the results in a separate column. Is there a simple way to perform this operation? I seem to remember seeing an article relative to this but can't put my hands on it now. Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple selection criteria
No I want to have a column with the names of those who meet multiple
criteria. I actually need several columns with employees who meet difference sets of criteria so auto filter won't provide what I need. "Pimamedic" wrote: What about trying Filter Auto FIlter "Bigkahuna" wrote: I have a worksheet with employee name, whether they are certified and their function in separate columns. I want to use a formula to select only those employees who are certified and perform the audit function and put the results in a separate column. Is there a simple way to perform this operation? I seem to remember seeing an article relative to this but can't put my hands on it now. Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple selection criteria
Try this array formula**. Note that this is slow to claculate if you have
1000's of rows of data. Named ranges: Name, refers to A2:A100 Status, refers to B2:B100 Enter a formula i a cell that returns the count of records that meet the criteria. E1: =COUNTIF(Status,"certified") Extract the names where the status is certified. Array entered** in E2: =IF(ROWS(E$2:E2)<=E$1,INDEX(Name,SMALL(IF(Status=" certified",ROW(Name)),ROWS(E$2:E2))-MIN(ROW(Name))+1),"") Copy down until you get blanks. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Bigkahuna" wrote in message ... No I want to have a column with the names of those who meet multiple criteria. I actually need several columns with employees who meet difference sets of criteria so auto filter won't provide what I need. "Pimamedic" wrote: What about trying Filter Auto FIlter "Bigkahuna" wrote: I have a worksheet with employee name, whether they are certified and their function in separate columns. I want to use a formula to select only those employees who are certified and perform the audit function and put the results in a separate column. Is there a simple way to perform this operation? I seem to remember seeing an article relative to this but can't put my hands on it now. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array with multiple selection criteria | Excel Worksheet Functions | |||
Ramdom selection with criteria | Excel Discussion (Misc queries) | |||
match multiple criteria ina range from multiple criteria multiplet | Excel Worksheet Functions | |||
Alternative criteria selection | Excel Discussion (Misc queries) | |||
Wildcard Selection Criteria | Excel Discussion (Misc queries) |