| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF FUNCTION?
I have attached a worksheet that includes a few individuals that were hired
which represents 'True' and termed which represent 'False.' I have over 10K rows and am trying to filter out only the information needed. This sheet was ran as of 12/31/08 and the formula I need is to tell me who was still active as of that date as you will see in the first row John Doe was hired 4/29/05 and it states true in column E (Active) John Doe is still active with the company and I need some formula that will show me all of those individuals who still state true, but in some cases there will be an individual such as Jane Andrew who has multiple hire and term dates but the last line states 'True' which means she was active as of 12/31/08. Also, Jane Doe was hired in 2008 and worked for 6 months or more and I need a function that will show me all those who were hired and termed in 2008 but worked for 6 months or more. Is this possible to find a solution that will at least help me with one of my problems. Last Name First Name Hire/Term Date Active Fulltime Permanent DOE JOHN 04/29/2005 TRUE FALSE FALSE DOE JANE 01/18/2008 TRUE FALSE FALSE DOE JANE 07/23/2008 FALSE TRUE TRUE SMITH JON 07/10/2007 FALSE FALSE FALSE SMITH JON 01/24/2008 FALSE FALSE FALSE DOE JUSTIN 05/23/2003 TRUE FALSE FALSE DOE JUSTIN 08/12/2003 FALSE FALSE FALSE ANDREW JANE 05/29/2003 TRUE FALSE TRUE ANDREW JANE 08/24/2005 FALSE FALSE FALSE ANDREW JANE 12/16/2005 TRUE FALSE FALSE ANDREW JANE 02/28/2007 FALSE FALSE FALSE ANDREW JANE 05/11/2007 TRUE FALSE FALSE ANDREW JANE 06/04/2007 FALSE FALSE FALSE ANDREW JANE 07/28/2007 TRUE FALSE FALSE ANDREW JANE 01/31/2008 FALSE TRUE TRUE ANDREW JANE 04/30/2008 TRUE TRUE TRUE I didn't know how to attach the spreadsheet. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF FUNCTION?
Do you want to count the number of people that meet your condtions or do you
want to create a True and False column so you can filter on them? -- If this helps, please click the Yes button Cheers, Shane Devenshire "Reenee" wrote: I have attached a worksheet that includes a few individuals that were hired which represents 'True' and termed which represent 'False.' I have over 10K rows and am trying to filter out only the information needed. This sheet was ran as of 12/31/08 and the formula I need is to tell me who was still active as of that date as you will see in the first row John Doe was hired 4/29/05 and it states true in column E (Active) John Doe is still active with the company and I need some formula that will show me all of those individuals who still state true, but in some cases there will be an individual such as Jane Andrew who has multiple hire and term dates but the last line states 'True' which means she was active as of 12/31/08. Also, Jane Doe was hired in 2008 and worked for 6 months or more and I need a function that will show me all those who were hired and termed in 2008 but worked for 6 months or more. Is this possible to find a solution that will at least help me with one of my problems. Last Name First Name Hire/Term Date Active Fulltime Permanent DOE JOHN 04/29/2005 TRUE FALSE FALSE DOE JANE 01/18/2008 TRUE FALSE FALSE DOE JANE 07/23/2008 FALSE TRUE TRUE SMITH JON 07/10/2007 FALSE FALSE FALSE SMITH JON 01/24/2008 FALSE FALSE FALSE DOE JUSTIN 05/23/2003 TRUE FALSE FALSE DOE JUSTIN 08/12/2003 FALSE FALSE FALSE ANDREW JANE 05/29/2003 TRUE FALSE TRUE ANDREW JANE 08/24/2005 FALSE FALSE FALSE ANDREW JANE 12/16/2005 TRUE FALSE FALSE ANDREW JANE 02/28/2007 FALSE FALSE FALSE ANDREW JANE 05/11/2007 TRUE FALSE FALSE ANDREW JANE 06/04/2007 FALSE FALSE FALSE ANDREW JANE 07/28/2007 TRUE FALSE FALSE ANDREW JANE 01/31/2008 FALSE TRUE TRUE ANDREW JANE 04/30/2008 TRUE TRUE TRUE I didn't know how to attach the spreadsheet. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF FUNCTION?
The best command is Autofilter.
Select the title raw of your table an go to: Data| Filter| AutoFilter small triangles will appear on each cell. By clicking them, you can select the items you have on those columns and filter you data. Is this what you are looking for click "Yes" at the bottom of this page if this helps you. "Reenee" wrote: I have attached a worksheet that includes a few individuals that were hired which represents 'True' and termed which represent 'False.' I have over 10K rows and am trying to filter out only the information needed. This sheet was ran as of 12/31/08 and the formula I need is to tell me who was still active as of that date as you will see in the first row John Doe was hired 4/29/05 and it states true in column E (Active) John Doe is still active with the company and I need some formula that will show me all of those individuals who still state true, but in some cases there will be an individual such as Jane Andrew who has multiple hire and term dates but the last line states 'True' which means she was active as of 12/31/08. Also, Jane Doe was hired in 2008 and worked for 6 months or more and I need a function that will show me all those who were hired and termed in 2008 but worked for 6 months or more. Is this possible to find a solution that will at least help me with one of my problems. Last Name First Name Hire/Term Date Active Fulltime Permanent DOE JOHN 04/29/2005 TRUE FALSE FALSE DOE JANE 01/18/2008 TRUE FALSE FALSE DOE JANE 07/23/2008 FALSE TRUE TRUE SMITH JON 07/10/2007 FALSE FALSE FALSE SMITH JON 01/24/2008 FALSE FALSE FALSE DOE JUSTIN 05/23/2003 TRUE FALSE FALSE DOE JUSTIN 08/12/2003 FALSE FALSE FALSE ANDREW JANE 05/29/2003 TRUE FALSE TRUE ANDREW JANE 08/24/2005 FALSE FALSE FALSE ANDREW JANE 12/16/2005 TRUE FALSE FALSE ANDREW JANE 02/28/2007 FALSE FALSE FALSE ANDREW JANE 05/11/2007 TRUE FALSE FALSE ANDREW JANE 06/04/2007 FALSE FALSE FALSE ANDREW JANE 07/28/2007 TRUE FALSE FALSE ANDREW JANE 01/31/2008 FALSE TRUE TRUE ANDREW JANE 04/30/2008 TRUE TRUE TRUE I didn't know how to attach the spreadsheet. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF FUNCTION?
I would like to create a column that states True if they're still active or
worked 6 months or more and false if they are no longer active. "Shane Devenshire" wrote: Do you want to count the number of people that meet your condtions or do you want to create a True and False column so you can filter on them? -- If this helps, please click the Yes button Cheers, Shane Devenshire "Reenee" wrote: I have attached a worksheet that includes a few individuals that were hired which represents 'True' and termed which represent 'False.' I have over 10K rows and am trying to filter out only the information needed. This sheet was ran as of 12/31/08 and the formula I need is to tell me who was still active as of that date as you will see in the first row John Doe was hired 4/29/05 and it states true in column E (Active) John Doe is still active with the company and I need some formula that will show me all of those individuals who still state true, but in some cases there will be an individual such as Jane Andrew who has multiple hire and term dates but the last line states 'True' which means she was active as of 12/31/08. Also, Jane Doe was hired in 2008 and worked for 6 months or more and I need a function that will show me all those who were hired and termed in 2008 but worked for 6 months or more. Is this possible to find a solution that will at least help me with one of my problems. Last Name First Name Hire/Term Date Active Fulltime Permanent DOE JOHN 04/29/2005 TRUE FALSE FALSE DOE JANE 01/18/2008 TRUE FALSE FALSE DOE JANE 07/23/2008 FALSE TRUE TRUE SMITH JON 07/10/2007 FALSE FALSE FALSE SMITH JON 01/24/2008 FALSE FALSE FALSE DOE JUSTIN 05/23/2003 TRUE FALSE FALSE DOE JUSTIN 08/12/2003 FALSE FALSE FALSE ANDREW JANE 05/29/2003 TRUE FALSE TRUE ANDREW JANE 08/24/2005 FALSE FALSE FALSE ANDREW JANE 12/16/2005 TRUE FALSE FALSE ANDREW JANE 02/28/2007 FALSE FALSE FALSE ANDREW JANE 05/11/2007 TRUE FALSE FALSE ANDREW JANE 06/04/2007 FALSE FALSE FALSE ANDREW JANE 07/28/2007 TRUE FALSE FALSE ANDREW JANE 01/31/2008 FALSE TRUE TRUE ANDREW JANE 04/30/2008 TRUE TRUE TRUE I didn't know how to attach the spreadsheet. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF FUNCTION?
Data filter will only show me the trues, but I would rather create a column
that states true if they are active as of 12/31 or have worked for 6 months or more and false for those who never made it to 2008 because there will be less trues for me to filter through versus looking at all of them. "Khoshravan" wrote: The best command is Autofilter. Select the title raw of your table an go to: Data| Filter| AutoFilter small triangles will appear on each cell. By clicking them, you can select the items you have on those columns and filter you data. Is this what you are looking for click "Yes" at the bottom of this page if this helps you. "Reenee" wrote: I have attached a worksheet that includes a few individuals that were hired which represents 'True' and termed which represent 'False.' I have over 10K rows and am trying to filter out only the information needed. This sheet was ran as of 12/31/08 and the formula I need is to tell me who was still active as of that date as you will see in the first row John Doe was hired 4/29/05 and it states true in column E (Active) John Doe is still active with the company and I need some formula that will show me all of those individuals who still state true, but in some cases there will be an individual such as Jane Andrew who has multiple hire and term dates but the last line states 'True' which means she was active as of 12/31/08. Also, Jane Doe was hired in 2008 and worked for 6 months or more and I need a function that will show me all those who were hired and termed in 2008 but worked for 6 months or more. Is this possible to find a solution that will at least help me with one of my problems. Last Name First Name Hire/Term Date Active Fulltime Permanent DOE JOHN 04/29/2005 TRUE FALSE FALSE DOE JANE 01/18/2008 TRUE FALSE FALSE DOE JANE 07/23/2008 FALSE TRUE TRUE SMITH JON 07/10/2007 FALSE FALSE FALSE SMITH JON 01/24/2008 FALSE FALSE FALSE DOE JUSTIN 05/23/2003 TRUE FALSE FALSE DOE JUSTIN 08/12/2003 FALSE FALSE FALSE ANDREW JANE 05/29/2003 TRUE FALSE TRUE ANDREW JANE 08/24/2005 FALSE FALSE FALSE ANDREW JANE 12/16/2005 TRUE FALSE FALSE ANDREW JANE 02/28/2007 FALSE FALSE FALSE ANDREW JANE 05/11/2007 TRUE FALSE FALSE ANDREW JANE 06/04/2007 FALSE FALSE FALSE ANDREW JANE 07/28/2007 TRUE FALSE FALSE ANDREW JANE 01/31/2008 FALSE TRUE TRUE ANDREW JANE 04/30/2008 TRUE TRUE TRUE I didn't know how to attach the spreadsheet. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF FUNCTION?
To mark the row with TRUE if it is the last row containing someone who is
still active then try this array formula: =AND(INDEX($D$2:$D$17,MATCH(MAX(($A$2:$A$17&$B$2:$ B$17=A2&B2)*$C$2:$C$17),($A$2:$A$17&$B$2:$B$17=A2& B2)*$C$2:$C$17,0)),MATCH(MAX(($A$2:$A$17&$B$2:$B$1 7=A2&B2)*$C$2:$C$17),($A$2:$A$17&$B$2:$B$17=A2&B2) *$C$2:$C$17,0)+1=ROW()) To enter an array press Shift+Ctrl+Enter not Enter. This assumes you have titles on row 1 and data starts in A2. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Reenee" wrote: I have attached a worksheet that includes a few individuals that were hired which represents 'True' and termed which represent 'False.' I have over 10K rows and am trying to filter out only the information needed. This sheet was ran as of 12/31/08 and the formula I need is to tell me who was still active as of that date as you will see in the first row John Doe was hired 4/29/05 and it states true in column E (Active) John Doe is still active with the company and I need some formula that will show me all of those individuals who still state true, but in some cases there will be an individual such as Jane Andrew who has multiple hire and term dates but the last line states 'True' which means she was active as of 12/31/08. Also, Jane Doe was hired in 2008 and worked for 6 months or more and I need a function that will show me all those who were hired and termed in 2008 but worked for 6 months or more. Is this possible to find a solution that will at least help me with one of my problems. Last Name First Name Hire/Term Date Active Fulltime Permanent DOE JOHN 04/29/2005 TRUE FALSE FALSE DOE JANE 01/18/2008 TRUE FALSE FALSE DOE JANE 07/23/2008 FALSE TRUE TRUE SMITH JON 07/10/2007 FALSE FALSE FALSE SMITH JON 01/24/2008 FALSE FALSE FALSE DOE JUSTIN 05/23/2003 TRUE FALSE FALSE DOE JUSTIN 08/12/2003 FALSE FALSE FALSE ANDREW JANE 05/29/2003 TRUE FALSE TRUE ANDREW JANE 08/24/2005 FALSE FALSE FALSE ANDREW JANE 12/16/2005 TRUE FALSE FALSE ANDREW JANE 02/28/2007 FALSE FALSE FALSE ANDREW JANE 05/11/2007 TRUE FALSE FALSE ANDREW JANE 06/04/2007 FALSE FALSE FALSE ANDREW JANE 07/28/2007 TRUE FALSE FALSE ANDREW JANE 01/31/2008 FALSE TRUE TRUE ANDREW JANE 04/30/2008 TRUE TRUE TRUE I didn't know how to attach the spreadsheet. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF FUNCTION?
Col A to Col F you have data
Go to data | filter | advance filter | select copy to another location | List range A:B | copy to : H1 | check unique records only | ok you will get unquie names in Col H & I In cell J2 put this formula - Array function use Ctrl + Shift + Enter =INDEX($D$2:$D$17,MATCH(H2&I2,$A$2:$A$17&$B$2:$B$1 7,0)-1+COUNT(IF (A2:A17&B2:B17=H2&I2,))) You will get the true / false ..... in Col G - cell G2 put this formula and drag it down =IF(A2&B2=A1&B1,IF(D2<D1,C2-C1,0),0) you will get the values And now in Col K - Cell K2 put this Array Formula =IF(ISERROR(INT(SUM(IF(H2&I2=$A$2:$A$17&$B$2:$B$17 ,IF($D$2:$D$17=FALSE, $G$2:$G$17)))/30)),"",INT(SUM(IF(H2&I2=$A$2:$A$17&$B$2:$B$17,IF( $D$2:$D $17=FALSE,$G$2:$G$17)))/30)) You will get total months worked. On Feb 2, 10:49*am, Reenee wrote: Data filter will only show me the trues, but I would rather create a column that states true if they are active as of 12/31 or have worked for 6 months or more and false for those who never made it to 2008 because there will be less trues for me to filter through versus looking at all of them. "Khoshravan" wrote: The best command is Autofilter. Select the title raw of your table an go to: Data| Filter| AutoFilter small triangles will appear on each cell. By clicking them, you can select the items you have on those columns and filter you data. Is this what you are looking for click "Yes" at the bottom of this page if this helps you. "Reenee" wrote: I have attached a worksheet that includes a few individuals that were hired which represents 'True' and termed which represent 'False.' I have over 10K rows and am trying to filter out only the information needed. This sheet was ran as of 12/31/08 and the formula I need is to tell me who was still active as of that date as you will see in the first row John Doe was hired 4/29/05 and it states true in column E (Active) John Doe is still active with the company and I need some formula that will show me all of those individuals who still state true, but in some cases there will be an individual such as Jane Andrew who has multiple hire and term dates but the last line states 'True' which means she was active as of 12/31/08. Also, Jane Doe was hired in 2008 and worked for 6 months or more and I need a function that will show me all those who were hired and termed in 2008 but worked for 6 months or more. Is this possible to find a solution that will at least help me with one of my problems. Last Name * * First Name * * *Hire/Term Date *Active *Fulltime * * * *Permanent DOE * JOHN * *04/29/2005 * * *TRUE * *FALSE * FALSE DOE * JANE * *01/18/2008 * * *TRUE * *FALSE * FALSE DOE * JANE * *07/23/2008 * * *FALSE * TRUE * *TRUE SMITH JON * * 07/10/2007 * * *FALSE * FALSE * FALSE SMITH JON * * 01/24/2008 * * *FALSE * FALSE * FALSE DOE * JUSTIN *05/23/2003 * * *TRUE * *FALSE * FALSE DOE * JUSTIN *08/12/2003 * * *FALSE * FALSE * FALSE ANDREW * * * *JANE * *05/29/2003 * * *TRUE * *FALSE * TRUE ANDREW * * * *JANE * *08/24/2005 * * *FALSE * FALSE * FALSE ANDREW * * * *JANE * *12/16/2005 * * *TRUE * *FALSE * FALSE ANDREW * * * *JANE * *02/28/2007 * * *FALSE * FALSE * FALSE ANDREW * * * *JANE * *05/11/2007 * * *TRUE * *FALSE * FALSE ANDREW * * * *JANE * *06/04/2007 * * *FALSE * FALSE * FALSE ANDREW * * * *JANE * *07/28/2007 * * *TRUE * *FALSE * FALSE ANDREW * * * *JANE * *01/31/2008 * * *FALSE * TRUE * *TRUE ANDREW * * * *JANE * *04/30/2008 * * *TRUE * *TRUE * *TRUE I didn't know how to attach the spreadsheet.- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
| LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
| Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
| Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
| Nested IF Function, Date Comparing, and NetworkDays Function | Excel Worksheet Functions | |||
Linear Mode
