Hi Guys,
In a sheet I have a lot data (rows and different headers).
This is my header looks like:
<tbody>
</tbody>
Step 1. Sort Case Number then Edited Date
Date/Time Opened is duplicate but identical to all duplicate Case Number: 10/12/2016
Step 2.
Now, the problem is I got a several duplicate Case Number (rows 2 to 10 duplicate Case Number 12345, then rows 11 to 13 duplicate case number 12344).
I know some excel formulas to get the unique Case Number, I'm using: =COUNTIF($C$2:C2,C2), I'll get 1 values, then I'll transfer the rest to a different sheet, then.
I need to calculate the turn around time of the process of the date using =NETWORKDAYS(Date/Time Opened, Edited Date, minus Holidays)-1
But here's the headache, How can I get the latest Edited Date from the 2nd source sheet? I know how to use V-LookUps, Index Match and so on. But How can I get the dates.
<tbody>
</tbody>
Any helps are welcome.
In a sheet I have a lot data (rows and different headers).
This is my header looks like:
Date/Time Opened | Case Number | SO Number/Quote Number | Subject | Description | PO Value | Case Owner | Edited By | Edit Date | Case Date/Time Last Modified | Date/Time Closed | To | Age | Account Country | Last Activity | Time Spent (Minutes) | Old Value | New Value | Status | Field / Event |
<tbody>
</tbody>
Step 1. Sort Case Number then Edited Date
Date/Time Opened is duplicate but identical to all duplicate Case Number: 10/12/2016
Step 2.
Now, the problem is I got a several duplicate Case Number (rows 2 to 10 duplicate Case Number 12345, then rows 11 to 13 duplicate case number 12344).
I know some excel formulas to get the unique Case Number, I'm using: =COUNTIF($C$2:C2,C2), I'll get 1 values, then I'll transfer the rest to a different sheet, then.
I need to calculate the turn around time of the process of the date using =NETWORKDAYS(Date/Time Opened, Edited Date, minus Holidays)-1
But here's the headache, How can I get the latest Edited Date from the 2nd source sheet? I know how to use V-LookUps, Index Match and so on. But How can I get the dates.
x | Date/Time Opened | Case Number | Edit Date |
1 | 11/24/16 21:19 | 00882831 | 11/24/16 21:19 |
2 | 11/24/16 21:19 | 00882831 | 11/24/16 21:19 |
3 | 11/24/16 21:19 | 00882831 | 11/24/16 21:52 |
4 | 11/24/16 21:19 | 00882831 | 11/24/16 21:58 |
5 | 11/24/16 21:19 | 00882831 | 11/24/16 23:21 |
<tbody>
</tbody>
Any helps are welcome.
Last edited: