Retrieving Certain Data

[lenze]

Here's what I have. A Table that looks like

   A           B             C      D       E   
 1 SS#         NAME          IN/OUT DATE    NUM 
 2 222-22-2222 Jones, Ted    IN     2/28/09 17  
 3 333-33-3333 Brown, Sam    IN     3/1/09  18  
 4 222-22-2222 Jones, Ted    OUT    3/3/09  N/A 
 5 444-44-4444 White, George IN     3/1/03  20  
 6 333-33-3333 Brown, Sam    OUT    3/3/09  N/A 
 7 333-33-3333 Brown, Sam    IN     3/4/09  21  
 8 111-11-1111 Smith, Bill   IN     3/5/09  22  
 9 555-55-5555 Taylor, Jim   IN     3/5/09  14  
10 666-66-6666 Johnson, Bill IN     2/6/09  16  

IN-OUT

[Table-It] version 09 by Erik Van Geit

I need a way to produce a daily list like this

   A             B   
19 NAME          NUM 
20 White, George 20  
21 Brown, Sam    21  
22 Smith, Bill   22  
23 Taylor, Jim   14  
24 Johnson, Bill 16  

IN-OUT

[Table-It] version 09 by Erik Van Geit

What I need, then, is a List of Names with their NUM that are IN and have not checked out.

Thx in Advance
lenze

 

[Juan Pablo González]

Lenze,

Assuming your data, as provided with headers, is in A1:E10, I put the following:

in F1

="Checked Out?"

in F2

=IF(C2="OUT",TRUE,IF(SUMPRODUCT(($C2:$C$10="OUT")*($A2:$A$10=A2))>0,TRUE,FALSE))

and dragged down till F10.

Then I created a Pivot Table with A1:F10 as the source, put "Checked Out?" as a page filter with FALSE, added "DATE" and "NAME" to the row fields, and added "NUM" to the values (Make sure it's set to Sum of NUM).

Hope that helps.

 

[lenze]

Thanks Juan: It works great. Even sorts the Names
lenze

 

[Oaktree]

Just to be thorough: Is it possible that someone could check in & out more than once?

A combination of In/Out/In would be absent from your list...

 

[lenze]

Just to be thorough: Is it possible that someone could check in & out more than once?

A combination of IN/Out/IN would be absent from your list...

No, I don't thinsk so. If you look at my sample data, "Brown, Sam" is IN/OUT/IN and Juan's PT picks him up as being IN.
lenze

 

[Oaktree]

Indeed, please disregard my previous note