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