Finding the Top 5 Under Certain Conditions and Counting

[JustBrowsing305]

Good Morning,

I've run into a strange scenario that demands a formula that can count an Employee ID under two different conditions, and THEN retrieve the name of the top 5 employees. The table or example that I will be using is shortened because the one I'm working with is too large. It's simple on a Pivottable but I'm trying to include a "Calculation" worksheet where I can link a Word page to extract certain cells. Below is the example table and the dilemma I'm facing:

Current Date:	1/18/2018
Area:	Jacksonville
Date:	Area:	Employee ID:	Order ID:
1/17/2018	Jacksonville	EK1	55829
1/17/2018	Jacksonville	EK1	55830
1/17/2018	Miami	58F	55831
1/17/2018	Ocala	30J	55832
1/18/2018	Jacksonville	EK1	55833
1/18/2018	Jacksonville	EK1	55834
1/18/2018	Jacksonville	EJ1	55835
1/18/2018	Jacksonville	EJ1	55836
1/18/2018	Jacksonville	EK1	55837
1/18/2018	Ocala	30J	55838
1/18/2018	Jacksonville	EK1	55839
1/18/2018	Jacksonville	EJ1	55840
1/18/2018	Jacksonville	EK1	55841
1/18/2018	Jacksonville	EJ1	55842
1/18/2018	Jacksonville	KG1	55843
1/18/2018	Jacksonville	KG1	55844
1/18/2018	Jacksonville	KG1	55845
1/18/2018	Jacksonville	AZ1	55846
1/18/2018	Jacksonville	AZ1	55847
1/18/2018	Jacksonville	KL1	55848

<tbody>
</tbody><colgroup><col><col><col><col></colgroup>

The formula that I'm trying to create will need to do the following:
1) Count the amount of Order IDs ONLY if the "Current Date" matches and the "Area" matches.
2) Retrieve the Five top Employee IDs (with most IDs) and place them in a Descending Order.

Under the example that I used, I know that the Order will be:
1) EK1 with 5 Order IDs
2) EJ1 with 4 Order IDs
3) KG1 with 3 Order IDs
4) AZ1 with 2 Order IDs
5) KL1 with 1 Order IDs.

I apologize if I'm seeming lazy however the worksheet I'm working with has 1,360 different Employee IDs and 34,547 different order IDs. I can't use a pivottable because I need to keep certain cells locked in my "calculation" sheet. I only need a formula that shows me the top 5 under the two conditions. Thank you in advance to anyone who reads this.

 

[Eric W]

Try:

	A	B	C	D	E	F	G	H
1	Current Date:	1/18/2018					Employee ID	# of Order IDs
2	Area:	Jacksonville					EK1	5
3							EJ1	4
4	Date:	Area:	Employee ID:	Order ID:			KG1	3
5	1/17/2018	Jacksonville	EK1	55829			AZ1	2
6	1/17/2018	Jacksonville	EK1	55830			KL1	1
7	1/17/2018	Miami	58F	55831				0
8	1/17/2018	Ocala	30J	55832				0
9	1/18/2018	Jacksonville	EK1	55833				0
10	1/18/2018	Jacksonville	EK1	55834				0
11	1/18/2018	Jacksonville	EJ1	55835				0
12	1/18/2018	Jacksonville	EJ1	55836				0
13	1/18/2018	Jacksonville	EK1	55837
14	1/18/2018	Ocala	30J	55838
15	1/18/2018	Jacksonville	EK1	55839
16	1/18/2018	Jacksonville	EJ1	55840
17	1/18/2018	Jacksonville	EK1	55841
18	1/18/2018	Jacksonville	EJ1	55842
19	1/18/2018	Jacksonville	KG1	55843
20	1/18/2018	Jacksonville	KG1	55844
21	1/18/2018	Jacksonville	KG1	55845
22	1/18/2018	Jacksonville	AZ1	55846
23	1/18/2018	Jacksonville	AZ1	55847
24	1/18/2018	Jacksonville	KL1	55848

<tbody>
</tbody>

Sheet5​

Worksheet Formulas Cell Formula H2 =COUNTIFS($A$5:$A$24,$B$1,$B$5:$B$24,$B$2,$C$5:$C$24,G2) <tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula G2 {=IFERROR(INDEX($C$5:$C$24,MATCH(1/(1/MAX(COUNTIFS($A$5:$A$24,$B$1,$B$5:$B$24,$B$2,$C$5:$C$24,$C$5:$C$24)*(COUNTIF($G$1:$G1,$C$5:$C$24)=0))),COUNTIFS($A$5:$A$24,$B$1,$B$5:$B$24,$B$2,$C$5:$C$24,$C$5:$C$24),0)),"")} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Edit: This does NOT handle ties well! I'll see if I can remedy that.

 

[Eric W]

This handles ties: (G2)

=IFERROR(INDEX($C$5:$C$24,MATCH(1/(1/MAX(COUNTIFS($A$5:$A$24,$B$1,$B$5:$B$24,$B$2,$C$5:$C$24,$C$5:$C$24)*(COUNTIF($G$1:$G1,$C$5:$C$24)=0))),COUNTIFS($A$5:$A$24,$B$1,$B$5:$B$24,$B$2,$C$5:$C$24,$C$5:$C$24)*(COUNTIF($G$1:$G1,$C$5:$C$24)=0),0)),"")

with Control+Shift+Enter.

 

[JustBrowsing305]

Thank you so much for your Reply Eric. I really do appreciate it. I just have one small question. In my scenario we have over 1200-1300 employees throughout the State, and over 34,000 incidents (rows) and every day only adds more and more, so when I implemented the formula (changing the numbers and columns to the worksheet that I'm working with), it crashed excel and excel crashed my work laptop.. I'm assuming it's that the formula is too demanding on the processors. Is there any other way to pull these 5 Employee IDs without such a demanding formula? On a side note, thank you so much I was reading through the formula and going through the logic and am simply amazed, I never would've thought of that.

 

[Fazza]

It's simple on a Pivottable but I'm trying to include a "Calculation" worksheet where I can link a Word page to extract certain cells.

Can you have the simple pivot table solution and then the calculation worksheet gets the results from there?

 

[JustBrowsing305]

Thank you for your reply. I'm working on a formula based on that idea and what I bumped into is that IF I make the pivottable, I will put a formula that searches for the Area (Example: Jacksonville) then searches for the first row to the right of it. THEN I need the formula to give me the next 4 Employee ID's after the first result. Is anyone familiar with rows formula? I will need for it to give me the first result (Employee with most Order IDs) of the list, then give me the next 4 while still searching under the criteria of the set Area.

 

[Eric W]

I'm not a whiz with pivot tables, so that may be the way to go, but I can't help much there. And I doubt that my formula can be made more efficient, or even another formula. You're just looking at too many cells. One final thought is to try an on-demand macro that calculates the list for you.

Open a COPY of your workbook. Right click on the sheet tab on the bottom and select View Code. From the menu bar, select Insert > Module. Paste the following code to the window that opens.

Public Sub Top5()
Dim MyData As Variant, MyArea As String, MyResults As Range, MyDict As Object
Dim i As Long, MyDate As Variant

    MyDate = Sheets("Sheet5").Range("B1").Value
    MyArea = Sheets("Sheet5").Range("B2").Value
    MyData = Sheets("Sheet5").Range("A5:D24").Value
    Set MyResults = Sheets("Sheet5").Range("J:K")
    
    Set MyDict = CreateObject("Scripting.Dictionary")
    MyDict("Employee ID") = "# of Order IDs"
    For i = 1 To UBound(MyData)
        If MyData(i, 1) = MyDate And MyData(i, 2) = MyArea Then MyDict(MyData(i, 3)) = MyDict(MyData(i, 3)) + 1
    Next i
    
    MyResults.ClearContents
    MyResults.Resize(MyDict.Count, 1) = WorksheetFunction.Transpose(MyDict.keys)
    MyResults.Offset(, 1).Resize(MyDict.Count, 1) = WorksheetFunction.Transpose(MyDict.items)
    
    With Worksheets("Sheet5").Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("K2"), Order:=xlDescending
        .SetRange Range("J:K")
        .Header = xlYes
        .Orientation = xlTopToBottom
        .Apply
    End With
    
    Sheets("Sheet5").Range("K7").Resize(MyDict.Count, 2).ClearContents
            
End Sub

Change the sheet references in red to match your sheet. There should be nothing in the result columns. Return to your Excel sheet and press Alt-F8. Select Top5 from the list and click Run. This should run pretty fast.

 

[JustBrowsing305]

Eric,

Thank you so much. I am absolutely clueless about VBA and programming/coding inside VBA but it worked. Thank you so much and thank you to the community for helping.

 

[Eric W]

Glad we could help!