Hi all,
I need to count the total time spent for one task among various people, by counting the starting time and ending time of the sub-events.
Example here:
<tbody>
</tbody>
**"S" represents starting time and "E" represents ending time.
"Time" represents time required for the whole task ( Event 1's E- Event 1's S + Event 2's E - Event's S+...)
However, sometimes, 2 different events can be completed in the same period, i.e. with same starting time and same ending time. We treat this as "duplicated pairs of data".
(Refer to "Brian's case, he completed both event 1 and 2 in the same period. )
Only two sets of starting date and ending dates are identical would be considered as "duplicated". If it has same starting date but different ending date, it is not being considered as "duplicated pairs of data", and vice versa.
I tried to use the Excel function (Data-->Remove Duplicates), but it checks basis on each cell but not a pair of data.
Using "Brian"'s case as example again, if I the "Remove Duplicates" function, the starting date in Event 3 will be removed as well, but I only want to remove Event 2, the identical pair of data.
I tried to create a new column for group data in S and data in E to make a new distinguishable code. ( by =S1+E1, and change it as "General' format)
<tbody>
</tbody>
Although this helps me to identify/ delete the duplicated data in "Group column", I still need to delete the corresponding pair of duplicated data manually, for my future counting of time.
I have a thousands of fields to check and this is not efficient to check one by one. I wanna know what can I do, to fulfill all of the below:
1) find out the duplicated PAIR of data and
2) delete the duplicated PAIR of data and
3) The deletion of duplicated PAIR of data is based on individual person, but not for whole sheet.
( For example, Alex's event 3 starting and ending date is same as Charles's event 1 one, but this should not be deleted).
Thank you!
I need to count the total time spent for one task among various people, by counting the starting time and ending time of the sub-events.
Example here:
| Event | 1 | 1 | 2 | 2 | 3 | 3 | Time |
| S | E | S | E | S | E | ||
| Alex | 1 Jan | 2 Feb | 3 Mar | 4 Apr | 5 May | 6 Jun | W days |
| Brian | 2 Jan | 2 Feb | 2 Jan | 2 Feb | 2 Jan | 3 Mar | X days |
| Charles | 5 May | 6 Jun | 7 Jul | 8 Aug | 9 Sep | 10 Oct | Y days |
| Danny | 2 Feb | 2 Feb | 3 Mar | 3 Mar | 4 Apr | 4 Apr | Z days |
<tbody>
</tbody>
**"S" represents starting time and "E" represents ending time.
"Time" represents time required for the whole task ( Event 1's E- Event 1's S + Event 2's E - Event's S+...)
However, sometimes, 2 different events can be completed in the same period, i.e. with same starting time and same ending time. We treat this as "duplicated pairs of data".
(Refer to "Brian's case, he completed both event 1 and 2 in the same period. )
Only two sets of starting date and ending dates are identical would be considered as "duplicated". If it has same starting date but different ending date, it is not being considered as "duplicated pairs of data", and vice versa.
I tried to use the Excel function (Data-->Remove Duplicates), but it checks basis on each cell but not a pair of data.
Using "Brian"'s case as example again, if I the "Remove Duplicates" function, the starting date in Event 3 will be removed as well, but I only want to remove Event 2, the identical pair of data.
I tried to create a new column for group data in S and data in E to make a new distinguishable code. ( by =S1+E1, and change it as "General' format)
| Event | 1 | 1 | Group | 2 | 2 | Group | 3 | 3 | Group | Time |
| S | E | S | E | S | E | |||||
| Alex | 1 Jan | 2 Feb | 85504 | 3 Mar | 4 Apr | 85626 | 5 May | 6 Jun | 85752 | W days |
| Brian | 2 Jan | 2 Feb | 85505 | 2 Jan | 2 Feb | 85505 | 2 Jan | 3 Mar | 85534 | X days |
| Charles | 5 May | 6 Jun | 85752 | 7 Jul | 8 Aug | 85878 | 9 Sep | 10 Oct | 86005 | Y days |
| Danny | 2 Feb | 2 Feb | 85536 | 3 Mar | 3 Mar | 85594 | 4 Apr | 4 Apr | 85658 | Z days |
<tbody>
</tbody>
Although this helps me to identify/ delete the duplicated data in "Group column", I still need to delete the corresponding pair of duplicated data manually, for my future counting of time.
I have a thousands of fields to check and this is not efficient to check one by one. I wanna know what can I do, to fulfill all of the below:
1) find out the duplicated PAIR of data and
2) delete the duplicated PAIR of data and
3) The deletion of duplicated PAIR of data is based on individual person, but not for whole sheet.
( For example, Alex's event 3 starting and ending date is same as Charles's event 1 one, but this should not be deleted).
Thank you!
