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:

[TABLE="width: 324"]
<tbody>[TR]
[TD]Current Date:[/TD]
[TD="align: right"]1/18/2018[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Area:[/TD]
[TD]Jacksonville[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Date:[/TD]
[TD]Area:[/TD]
[TD]Employee ID:[/TD]
[TD]Order ID:[/TD]
[/TR]
[TR]
[TD="align: right"]1/17/2018[/TD]
[TD]Jacksonville[/TD]
[TD]EK1[/TD]
[TD="align: right"]55829[/TD]
[/TR]
[TR]
[TD="align: right"]1/17/2018[/TD]
[TD]Jacksonville[/TD]
[TD]EK1[/TD]
[TD="align: right"]55830[/TD]
[/TR]
[TR]
[TD="align: right"]1/17/2018[/TD]
[TD]Miami[/TD]
[TD]58F[/TD]
[TD="align: right"]55831[/TD]
[/TR]
[TR]
[TD="align: right"]1/17/2018[/TD]
[TD]Ocala[/TD]
[TD]30J[/TD]
[TD="align: right"]55832[/TD]
[/TR]
[TR]
[TD="align: right"]1/18/2018[/TD]
[TD]Jacksonville[/TD]
[TD]EK1[/TD]
[TD="align: right"]55833[/TD]
[/TR]
[TR]
[TD="align: right"]1/18/2018[/TD]
[TD]Jacksonville[/TD]
[TD]EK1[/TD]
[TD="align: right"]55834[/TD]
[/TR]
[TR]
[TD="align: right"]1/18/2018[/TD]
[TD]Jacksonville[/TD]
[TD]EJ1[/TD]
[TD="align: right"]55835[/TD]
[/TR]
[TR]
[TD="align: right"]1/18/2018[/TD]
[TD]Jacksonville[/TD]
[TD]EJ1[/TD]
[TD="align: right"]55836[/TD]
[/TR]
[TR]
[TD="align: right"]1/18/2018[/TD]
[TD]Jacksonville[/TD]
[TD]EK1[/TD]
[TD="align: right"]55837[/TD]
[/TR]
[TR]
[TD="align: right"]1/18/2018[/TD]
[TD]Ocala[/TD]
[TD]30J[/TD]
[TD="align: right"]55838[/TD]
[/TR]
[TR]
[TD="align: right"]1/18/2018[/TD]
[TD]Jacksonville[/TD]
[TD]EK1[/TD]
[TD="align: right"]55839[/TD]
[/TR]
[TR]
[TD="align: right"]1/18/2018[/TD]
[TD]Jacksonville[/TD]
[TD]EJ1[/TD]
[TD="align: right"]55840[/TD]
[/TR]
[TR]
[TD="align: right"]1/18/2018[/TD]
[TD]Jacksonville[/TD]
[TD]EK1[/TD]
[TD="align: right"]55841[/TD]
[/TR]
[TR]
[TD="align: right"]1/18/2018[/TD]
[TD]Jacksonville[/TD]
[TD]EJ1[/TD]
[TD="align: right"]55842[/TD]
[/TR]
[TR]
[TD="align: right"]1/18/2018[/TD]
[TD]Jacksonville[/TD]
[TD]KG1[/TD]
[TD="align: right"]55843[/TD]
[/TR]
[TR]
[TD="align: right"]1/18/2018[/TD]
[TD]Jacksonville[/TD]
[TD]KG1[/TD]
[TD="align: right"]55844[/TD]
[/TR]
[TR]
[TD="align: right"]1/18/2018[/TD]
[TD]Jacksonville[/TD]
[TD]KG1[/TD]
[TD="align: right"]55845[/TD]
[/TR]
[TR]
[TD="align: right"]1/18/2018[/TD]
[TD]Jacksonville[/TD]
[TD]AZ1[/TD]
[TD="align: right"]55846[/TD]
[/TR]
[TR]
[TD="align: right"]1/18/2018[/TD]
[TD]Jacksonville[/TD]
[TD]AZ1[/TD]
[TD="align: right"]55847[/TD]
[/TR]
[TR]
[TD="align: right"]1/18/2018[/TD]
[TD]Jacksonville[/TD]
[TD]KL1[/TD]
[TD="align: right"]55848[/TD]
[/TR]
</tbody><colgroup><col><col><col><col></colgroup>[/TABLE]



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
Current Date:	Employee ID	# of Order IDs
Area:	Jacksonville	EK1
EJ1
Date:	Area:	Employee ID:	Order ID:	KG1
Jacksonville	EK1	AZ1
Jacksonville	EK1	KL1
Miami	58F
Ocala	30J
Jacksonville	EK1
Jacksonville	EK1
Jacksonville	EJ1
Jacksonville	EJ1
Jacksonville	EK1
Ocala	30J
Jacksonville	EK1
Jacksonville	EJ1
Jacksonville	EK1
Jacksonville	EJ1
Jacksonville	KG1
Jacksonville	KG1
Jacksonville	KG1
Jacksonville	AZ1
Jacksonville	AZ1
Jacksonville	KL1

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]1/18/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]5[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]4[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1/17/2018[/TD]

[TD="align: right"]55829[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]1/17/2018[/TD]

[TD="align: right"]55830[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]1/17/2018[/TD]

[TD="align: right"]55831[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]1/17/2018[/TD]

[TD="align: right"]55832[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]1/18/2018[/TD]

[TD="align: right"]55833[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]1/18/2018[/TD]

[TD="align: right"]55834[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]1/18/2018[/TD]

[TD="align: right"]55835[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]1/18/2018[/TD]

[TD="align: right"]55836[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]1/18/2018[/TD]

[TD="align: right"]55837[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]1/18/2018[/TD]

[TD="align: right"]55838[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]1/18/2018[/TD]

[TD="align: right"]55839[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]1/18/2018[/TD]

[TD="align: right"]55840[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]1/18/2018[/TD]

[TD="align: right"]55841[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]1/18/2018[/TD]

[TD="align: right"]55842[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]1/18/2018[/TD]

[TD="align: right"]55843[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]1/18/2018[/TD]

[TD="align: right"]55844[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]1/18/2018[/TD]

[TD="align: right"]55845[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: right"]1/18/2018[/TD]

[TD="align: right"]55846[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="align: right"]1/18/2018[/TD]

[TD="align: right"]55847[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="align: right"]1/18/2018[/TD]

[TD="align: right"]55848[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet5​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]H2[/TH]
[TD="align: left"]=COUNTIFS($A$5:$A$24,$B$1,$B$5:$B$24,$B$2,$C$5:$C$24,G2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]G2[/TH]
[TD="align: left"]{=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)),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



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!