Hi ,
The first task is to extract a unique list of names from the data on Sheet1 , where the names in column B are repeated several times.
This is done by the formula in A2 , which is :
=IFERROR(INDEX(Sheet1!$B$2:$B$51,MATCH(0,COUNTIF(A$1:A1,Sheet1!$B$2:$B$51),0)),"")
This is an array formula , and needs to be entered in a cell using CTRL SHIFT ENTER.
The range marked in BOLD is the range where your input data has the names of the employees.
The next task is to use the employee names to retrieve their access card numbers.
This is a standard INDEX + MATCH formula :
=IF($A2 = "", "", INDEX(Sheet1!$E$2:$E$51, MATCH($A2, Sheet1!$B$2:$B$51,0)))
The range in BOLD is the range which has the employee names , while the range in GREEN is the range which has the access card numbers of each employee.
While retrieving the Time In and Time Out values , the following are assumed :
1. The Time In and Time Out values alternate i.e. each Time In value must be followed by its Time Out. If this rule is not followed , it will be difficult to match a Time Out with its corresponding Time In.
2. The TIme In should be less than the Time Out.
Retrieving the values uses the SMALL function to find out the first time value , which is assumed to be a Time In , the second value which is assumed to be a Time Out , and so on , in alternating fashion. The formula itself is a standard one :
=IF($A2 = "", "", IFERROR(INDEX(Sheet1!$D$2:$D$51, SMALL(IF($A2 = Sheet1!$B$2:$B$51,ROW(Sheet1!$D$2:$D$51) - MIN(ROW(Sheet1!$D$2:$D$51)) + 1), COLUMNS($A:A))),""))
The range in BOLD is the range where the Event Time values are entered in the input data.
The function COLUMNS($A:A) returns 1 in the first column cell where it is entered ; when it is copied across to the next column cell , it changes to COLUMNS($A:B) which then returns 2 , and in the next column cell , it becomes COLUMNS($A:C) which returns 3 , and so on , which is how all the time values relating to one employee are retrieved.
Getting the time duration is just a matter of adding all the individual durations ; each duration is obtained by subtracting the Time In value from the corresponding Time Out value.
In case you wish to know anything in more detail , just ask.
Narayan