How to Total Hours spent Excluding duplicate values / Count excluding duplicate values

Hi Friends,

I have two queries which I hope many of you would be able to assist me with:

Query - 1:
Column C has ticket numbers which are duplicates. I would like to calculate the total time spent on the ticket including the duplicates, but the result should show only 1 ticket and total amount spent against it.

For Ex: Row 10 and Row 11 have duplicate ticket entry.

Day 1 - Ticket number 12345 times spent is captured under columns G, H, AC & AD. Assuming I have entered 2Hr 30Min into G, H, AC & AD

Day 2 - Same ticket 12345 time spent is captured under columns G, H, AC & AD. Assuming I have entered 2Hr into G, H, AC & AD

Wish list Result:
In a separate spread sheet the ticket number should be showing only once and the sum of two days spent on it should be totaled.

Separate spread sheet should show the results:

A B C
Ticket Hrs Min
12345 4 30
123 6 30
12 2 45

_____________________________________________________________________________

Query – 2:
Column C has ticket numbers which are duplicates. I would like to capture the last updated status against the ticket using the date field in Column “B” and present the count under the sheet “ShadowExecutor_TeamDashboard” against its status.

During this count, the earlier status against the same ticket should be excluded.

Ex: Row 10 and Row 11 have duplicate ticket entry.

Day 1 – Under Row 10 I select the date as 07 Feb 2014, enter the ticket number 12345; and select the status as “In Progress”

Day 2 – Under Row 11 I select the date as 10 Feb 2014, enter the ticket number 12345; and select the status as “Resolved”.

Wish list Result:
In my attached excel sheet, for Query 2, the total count of tickets shows as 4, where in the actual ticket count is 3 excluding duplicate. So, the ticket 12345 shows under “In Progress” & “Resolved” which should be excluded and should be representing the current status based on the last selected date in Column B.

So my dashboard should show:
Jan-14
Registered 0
Open 0
In Progress 1
On Hold 0
Resolved 2
Closed 0
Reopened 0


Appreciate your help

Hi Friends,

Please help me out in resolving my query. I am seeking solution from experts like you. Please let me know on how to achieve the above requests.