capture unique values and calculate

[bcamp1973]

ok, this is way over my head...maybe even impossible, but here's what i'm trying to accomplish. I want 4 different columns as shown below. In the "Time" and "Ticket" colums i'll track the total amount of time i spend on any given ticket. There may be repeat entries for a ticket. In the totals column I'd *like* it to automatically add an instance for each unique ticket dynamically. So, even though i have ticket #123 entered 3 times, it only shows it once. To make it more difficult, i then want to sum the time for all instances of #123 and place it in the column to the right...does that make any sense? Hope this helps...

TIME TICKET TOTALS

0:30 #123 #123 4:45
1:15 #123 #456 1:00
3:00 #123
1:00 #456
0:15 #456

[Tim M]

Have you tried using the 'subtotals' command?
you can go 'data'....'subtotals' then subtotal at each change in ticket, and
subtotal the 'time'
It won't end up looking exactly as you have shown in your example but it
should do what you want it to do.


"bcamp1973" wrote:

>
> ok, this is way over my head...maybe even impossible, but here's what
> i'm trying to accomplish. I want 4 different columns as shown below.
> In the "Time" and "Ticket" colums i'll track the total amount of time i
> spend on any given ticket. There may be repeat entries for a ticket. In
> the totals column I'd *like* it to automatically add an instance for
> each unique ticket dynamically. So, even though i have ticket #123
> entered 3 times, it only shows it once. To make it more difficult, i
> then want to sum the time for all instances of #123 and place it in the
> column to the right...does that make any sense? Hope this helps...
>
> TIME TICKET TOTALS
> ------ -------- --------- -------
> 0:30 #123 #123 4:45
> 1:15 #123 #456 1:00
> 3:00 #123
> 1:00 #456
> 0:15 #456
>
>
> --
> bcamp1973
> ------------------------------------------------------------------------
> bcamp1973's Profile: http://www.excelforum.com/member.php...o&userid=32268
> View this thread: http://www.excelforum.com/showthread...hreadid=539972
>
>

[Ron Coderre]

bcamp1973:

Here are a couple ideas:

1)Use a Pivot Table to summarize total time per ticket.
Set the function in the DATA area to Sum of Time
Custom Number Format the Sum of Time column in the Pivot Table as: [h]:mm:ss

OR

With your sample data in A1:B7

C2: #123
D2: =SUMIF($B$1:$B$10,$C2,$A$1:$A$10)
Custom Number Format that cell as: [h]:mm:ss

Do either of those give you something to work with?

Regards,
Ron

[bcamp1973]

Hi Ron, thanks for the feedback. I don't know much about pivot tables so i'm using your second suggestion. That's definitely a good start. Ideally i'd like to show the total just once instead of next to each instance, but this will hold me over...unless you have a suggestion of that of course

Cheers,
Brian

[Ron Coderre]

I think a Pivot Table would be the easiest approach, but since you prefer the formulas...See if this works for you:

In the example I previously posted, the formula in D2 calculates the total time in Col_A where the Col_B value matches C2. For that approach to work for all unique Col_B values, you'd need a list of those values. I'd use an Advanced Filter to build that list:

C1: TICKET (the same value as B1)
Select your data in columns A and B, including the column titles in Row_1.

<data><filter><advanced filter>
Check: Copy to another location
Check: Unique records only
List Range: (your already selected data)
Criteria Range: (leave this blank)
Copy To: $C$1
Click [OK]

That will create a list of unique Col_B values under C1

Now, copy the previously posted D2 formula down as far as you need it.

Does that help?

Regards,
Ron