Concurrent Transactions by Minute

[ExcelNate]

I need to know the maximum amount of concurrent transactions (calls, employees, etc.) that are happening during each transaction by minute and report that maximum for each transaction. My thought is to create a function where I can pass a start date/time, end date/time, a range of potentially concurrent start date/times and a range of potentially concurrent end date/times and then call that function from each transaction row. I'm thinking the UDF would take the start date/time, loop through the ranges to find concurrent transactions, and store the number of concurrent transactions in an array. The UDF would increment the start date/time by one minute and keep looping and storing the number of concurrent transactions until it hit the end date/time for that transaction. The UDF would then take the maximum number of concurrent transactions from the array and return that number back as the result of the function. The data would not need to be sorted for the UDF to work and I could copy and use the UDF on next month’s data by just referencing the correct transaction date/times and potentially concurrent date/times.

This seems complex to write and time-consuming to run, especially since once the function works I need to add one more complicating factor to only count concurrent transactions if another variable is also true (concurrent transaction times have to be for the same call center, employee, etc.)

Am I making this too hard? I’d really appreciate any advice as to how to create this function.

 

[shawnhet]

Personally, I would think that this seems like a good fit for a pivot table. However, I would suggest posting a sample of your data and what you want your output to look like.

Just FYI, here is a brief link on pivot tables.

Excel Pivot Table Tutorial

Cheers,

 

[ExcelNate]

Personally, I would think that this seems like a good fit for a pivot table. However, I would suggest posting a sample of your data and what you want your output to look like.

Just FYI, here is a brief link on pivot tables.

Excel Pivot Table Tutorial

Cheers,

Thanks for the reply. Here's what the data might look like (before it gets more complicated by adding a location/employee) :

[TABLE="width: 535"]
<tbody>[TR]
[TD]Transaction[/TD]
[TD]Start Time[/TD]
[TD]End Time[/TD]
[TD]Concurrent[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]2/15/2013 9:00 AM[/TD]
[TD="align: right"]2/15/2013 10:00 AM[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]2/15/2013 9:05 AM[/TD]
[TD="align: right"]2/15/2013 9:15 AM[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]2/15/2013 9:06 AM[/TD]
[TD="align: right"]2/15/2013 9:30 AM[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]2/15/2013 9:31 AM[/TD]
[TD="align: right"]2/15/2013 9:41 AM[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]2/15/2013 9:45 AM[/TD]
[TD="align: right"]2/15/2013 9:55 AM[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]2/15/2013 10:05 AM[/TD]
[TD="align: right"]2/15/2013 11:00 AM[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]


The Concurrent calculation (say Column D) is where I'd put the calculation. Say the first row of data is row 2. I'm thinking of a user defined function in D2 that would be something like =UDF(B2,C2,$B$2:$B$7,$C$2:$C$7). The UDF in cell D2 starts at 9:00 AM on 2/15 and finds 1 concurrent transaction, transaction 1. It loops through 9:01, 9:02, etc. all the way until 10:00 AM. At 9:07 (and all the way until 9:15), there are 3 concurrent transactions (transactions 1, 2, and 3), so the UDF returns 3 in cells D2 through D4. From 9:31 to 9:41, transaction 4 is only concurrent with transaction 1, so the maximum concurrent value for transaction 4 is 2, but transaction 1 still shows the maximum value, 3.

I've tried COUNTIFS and looked at SUMPRODUCT formulas, but so far they would tell me transaction 1 has 5 concurrent transactions (transactions 1-5) since transaction 1 overlaps some of each of the first 5 transactions. That's not what I'm after. I need to how many transactions are going on at any minute in time, not how many transactions overlap. Does my distinction/definition of concurrency make sense?

If there's a way to do that with a Pivot Table, I'm all ears, but it seems more complicated than a Pivot Table can handle.

Thanks again,

Nate

 

[shawnhet]

Ok, I see what you are going for here. You're right that it is quite a bit more complicated than I was thinking above. I agree that a VBA solution is the only likely option. Unfortunately, I won't have time to look at this for a couple of days.

Off the top of my head, I would suggest a VBA loop to expand your range above so that each individual transaction-minute has its own row. e.g transaction 1 would have a row for 9:01, for 9:02 and so on up to 9:59 and transaction 2 would have lines for 9:05 all the way to 9:14 and so on.

Then on this expanded range, you could use a Pivot table that can return the maximum concurrent for range or location or whatever other factors you would like to compare.

Cheers,

 

[ExcelNate]

Thanks very much. I took some time today to write a VBA procedure that does what I need. It's fairly time-consuming on 1,000 rows of data and gives the 8 processors on my PC something to do. If there's a better, faster solution, that would be great. If not, we'll make this work.