IF FUNCTION?

[Reenee]

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.

 

[Shane Devenshire]

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?

 

[Khoshravan]

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]

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.

 

[Reenee]

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.

 

[Shane Devenshire]

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$17=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.

 

[muddan madhu]

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$17,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.