Top Subset of Category with Most Recent as Tie Breaker

MagiCarty

New Member
Joined
Oct 4, 2013
Messages
12
I'm trying to pull back (based on Customer Number), their #1 Case Reason based on volume of cases, and then when there is a tie (like for 3CTI001-S or 3RTT001), bring back the most recent Case Reason as the winner (from the top tied groups).

I'm thinking there are some arrays and countifs involved here, but my brain hurts from the hours trying to figure this out. Many thanks for your help.



Excel 2010
ABCDEF
1Customer NumberCase ReasonDate/Time OpenedCustomer NumberTop Reason (should be)
2101W001-SInstallation3/7/2016 9:07101W001-SInstallation
3101W001-SInstallation3/7/2016 6:153CTI001Hardware Tech Support
4101W001-SInstallation2/24/2016 8:573CTI001-SHardware Tech Support
5101W001-SSoftware Tech Support6/30/2016 7:553DCO001Software Tech Support
6101W001-SSoftware Tech Support6/10/2016 5:543RTT001General Account Maintenance
73CTI001Software Tech Support9/22/2016 16:35405T001Installation
83CTI001General Account Maintenance3/16/2016 14:15
93CTI001Installation9/15/2016 13:19
103CTI001Hardware Tech Support7/14/2016 8:36
113CTI001Hardware Tech Support9/6/2016 12:24
123CTI001Hardware Tech Support9/1/2016 13:48
133CTI001-SGeneral Account Maintenance3/15/2016 13:24
143CTI001-SGeneral Inquiries2/2/2016 9:57
153CTI001-SHardware Tech Support3/30/2016 9:39
163DCO001Hardware Tech Support10/31/2016 9:21
173DCO001Hardware Tech Support10/31/2016 9:12
183DCO001Software Tech Support8/17/2016 8:27
193GCO001Software Tech Support10/31/2016 12:32
203GCO001Software Tech Support8/17/2016 8:27
213GCO001Software Tech Support8/9/2016 14:11
223RTT001General Account Maintenance11/11/2016 12:19
233RTT001General Account Maintenance9/6/2016 6:26
243RTT001General Inquiries7/14/2016 10:15
253RTT001General Inquiries7/11/2016 5:46
263RTT001Installation8/24/2016 12:37
273RTT001Installation7/9/2016 7:50
283RTT001Hardware Tech Support12/1/2016 9:39
29405T001Finance6/16/2016 13:42
30405T001Installation1/22/2016 13:39
31405T001Installation1/22/2016 12:06
32405T001Installation1/22/2016 11:17
33405T001Installation1/22/2016 9:48
test
 
Last edited:

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
I'm assuming that you're populating the customer name column yourself by some method. If need be, I can create a formula to list unique customer numbers. Given that, put this formula in F2:

=INDEX($B$2:$B$33,MATCH(MAX(FREQUENCY(IF($A$2:$A$33=E2,MATCH($B$2:$B$33,$B$2:$B$33,0)),ROW($B$2:$B$33)-ROW($B$2)+1)),FREQUENCY(IF($A$2:$A$33=E2,MATCH($B$2:$B$33,$B$2:$B$33,0)),ROW($B$2:$B$33)-ROW($B$2)+1),0))

and confirm with Control+Shift+Enter. One drawback of this formula is that in case of a tie, the Reason chosen is pretty much random. It's based on the first time the reason appears in column B, not the first time it appears for the given customer. If that's an issue, I might be able to come up with something based on the fact that the customers are sorted together.
 
Upvote 0
Hi Eric,

Yes, the customer name is static in a list like this. When I use your array formula, I'm getting General Account Maintenance for 3CTI001-S, Installation for 3RTT001, and a surprise of Hardware Tech Support for 3DCO001 which shows up only twice compared to 4x for Software Tech Support. Thanks again for your help.
 
Upvote 0
ABC
163DCO001Hardware Tech Support10/31/2016 9:21
173DCO001Hardware Tech Support10/31/2016 9:12
183DCO001Software Tech Support8/17/2016 8:27
193GCO001Software Tech Support10/31/2016 12:32
203GCO001Software Tech Support8/17/2016 8:27
213GCO001Software Tech Support8/9/2016 14:11

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
test



3DCO001 has 2 Hardware Tech Support and 1 Software Tech Support. 3GCO001 has 3 Software Tech Support.
 
Upvote 0
Oh shoot. My mistake. I meant to keep that all as one customer for my example. Is there a way to get 3CTI001-S and 3RTT001 to show up with their respective tie breakers based on most recent Case Reason?


Excel 2010
EFG
1Customer NumberTop Reason (should be)TEST
43CTI001-SHardware Tech SupportGeneral Account Maintenance
73RTT001General Account MaintenanceInstallation
test
 
Upvote 0
Try this array formula in F2, and drag down:

=INDEX($B$2:$B$33,MATCH(MAX(FREQUENCY(IF($A$2:$A$33=E2,MATCH($A$2:$A$33&$B$2:$B$33,$A$2:$A$33&$B$2:$B$33,0)),ROW($B$2:$B$33)-ROW($B$2)+1)+$C$2:$C$34/100000),FREQUENCY(IF($A$2:$A$33=E2,MATCH($A$2:$A$33&$B$2:$B$33,$A$2:$A$33&$B$2:$B$33,0)),ROW($B$2:$B$33)-ROW($B$2)+1)+$C$2:$C$34/100000,0))

with Control+Shift+Enter.

Note that when I add the C ranges to get the date, the range is 1 row bigger than the actual range. This is by design. Make sure that the row below your table is empty. In this limited test, it seems to work as you desire, and the logic is sound. But there's a possibility of rounding errors. Try it and let me know how it works for you.
 
Upvote 0
Upon thinking about that formula, I realized that it also has a flaw. I managed to correct it by using the new MAXIFS function in Excel 2016.

=INDEX($B$2:$B$33,MATCH(MAX(FREQUENCY(IF($A$2:$A$33=E2,MATCH($A$2:$A$33&$B$2:$B$33,$A$2:$A$33&$B$2:$B$33,0)),ROW($B$2:$B$33)-ROW($B$2)+1)+MAXIFS($C$2:$C$34,$A$2:$A$34,E2,$B$2:$B$34,$B$2:$B$34)/100000),FREQUENCY(IF($A$2:$A$33=E2,MATCH($A$2:$A$33&$B$2:$B$33,$A$2:$A$33&$B$2:$B$33,0)),ROW($B$2:$B$33)-ROW($B$2)+1)+MAXIFS($C$2:$C$34,$A$2:$A$34,E2,$B$2:$B$34,$B$2:$B$34)/100000,0))

I'm trying to think of a way to fix it without using MAXIFS. I'll let you know.
 
Last edited:
Upvote 0
Hey Eric,

Any luck creating this without MAXIFS? My machine is having a hard time processing your most recent solution (not the MAXIFS), as my actual data set is about 76k records. Thanks again for your help!
 
Upvote 0
Does your version support MAXIFS? I suppose that's really a bit irrelevant. If your PC struggles without it, the changes I'd have to make to get it to work would make it even more calculation-intensive.

Given that, you may want to consider a macro solution. Given the data in columns A:C, I can write a macro to list each customer number in E and the appropriate reason in F. If that's of interest, let me know.
 
Upvote 0
My corp machine is on 2010, so it does not. If you can develop a macro that will do the same thing, that would be really great. I can tweak it to meet my actual data set. Thank you.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top