Multiple date in a single identifier

[rplim2016]

Hi Guys,

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.

 

[Mel Smith]

Perhaps you might want to consider redesigning your sheet so that you have separate cells/columns for both "date/time opened" and "edit date/time" so that you can use the Networkdays function?

Mel