Complex MAX formula needed

[ShanaVT]

Hello All! I need to write a formula to pull out the Top 5 programs from a large set of of data. Example set of data is provided below. What I am trying to do is extract the Top 5 Groupings in column C below by ID number in column A along with their percentage of the total in the Amount column for Direct Project Account Codes only in column B in order of greatest to smallest. If the ID number has more than 5 Direct Groupings like for example ID 3 below then I want the total of the other Groupings to be summed under an Other column. The desired results in the desired format is below the sample data set.

ID	Project Account Code	Grouping	Amount
1​	Direct	Project A	250​
1​	Direct	Project A	100​
1​	Direct	Project B	500​
1​	Direct	Project C	50​
1​	B&P	INV A	150​
2​	Direct	Project C	25​
2​	Direct	Project C	600​
2​	Pre B&P	INV B	800​
2​	Suspense	SUSP A	100​
2​	Unallowable	UNALLOW D	25​
3​	Direct	Project A	1000​
3​	Direct	Project B	900​
3​	Direct	Project C	50​
3​	Direct	Project D	250​
3​	Direct	Project E	400​
3​	Direct	Project F	10​
3​	Direct	Project G	25​

Below is the answer I am looking to get in this format from the data set below using a formula. Lists the ID in the first column and then 2 columns per Top 5 Grouping and then 2 columns for other where the first column under each Grouping is for the Project Name and the second is for the % of the amount - these are in order from greatest to smallest. Where a particular ID had less than 5 Direct Projects those are blank. Again, only focused on Direct Project Account Group Codes even though the data set above has multiple codes.

ID

Grouping 1​

​

Grouping 2​

​

Grouping 3​

​

Grouping 4

Grouping 5

Other

1​

Project B

56%​

Project A

39%​

Project C

6%​

2​

Project C

100%​

3​

Project A

38%​

Project B

34%​

Project E

15%​

Project D

9%​

Project E

2%​

Other

1%​

Any help much appreciated! Thanks!

Shana

 

[Eric W]

Hi Shana,

It can be done:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	ID	Project Account Code	Grouping	Amount		ID	Grouping 1		Grouping 2		Grouping 3		Grouping 4		Grouping 5		Other
2	1	Direct	Project A	250		1	Project B	56%	Project A	39%	Project C	6%
3	1	Direct	Project A	100		2	Project C	100%
4	1	Direct	Project B	500		3	Project A	38%	Project B	34%	Project E	15%	Project D	9%	Project C	2%	Other	1%
5	1	Direct	Project C	50
6	1	B&P	INV A	150
7	2	Direct	Project C	25
8	2	Direct	Project C	600
9	2	Pre B&P	INV B	800
10	2	Suspense	SUSP A	100
11	2	Unallowable	UNALLOW D	25
12	3	Direct	Project A	1000
13	3	Direct	Project B	900
14	3	Direct	Project C	50
15	3	Direct	Project D	250
16	3	Direct	Project E	400
17	3	Direct	Project F	10
18	3	Direct	Project G	25
Sheet11

Cell Formulas
Range	Formula
G2:G4,O2:O4,M2:M4,K2:K4,I2:I4	G2	=IF(H2<>"",INDEX($C:$C,AGGREGATE(15,6,ROW($C$2:$C$18)/($A$2:$A$18=$F2)/($B$2:$B$18="Direct")/(SUMIFS($D$2:$D$18,$A$2:$A$18,$F2,$B$2:$B$18,"Direct",$C$2:$C$18,$C$2:$C$18)/(SUMIFS($D$2:$D$18,$A$2:$A$18,$F2,$B$2:$B$18,"Direct"))=H2)/(COUNTIFS($F2:F2,$C$2:$C$18)=0),1)),"")
H2:H4,P2:P4,N2:N4,L2:L4,J2:J4	H2	=IFERROR(AGGREGATE(14,6,SUMIFS($D$2:$D$18,$A$2:$A$18,$F2,$B$2:$B$18,"Direct",$C$2:$C$18,$C$2:$C$18)/($A$2:$A$18=$F2)/($B$2:$B$18="Direct")/(COUNTIF($F2:F2,$C$2:$C$18)=0),1)/SUMIFS($D$2:$D$18,$A$2:$A$18,$F2,$B$2:$B$18,"Direct"),"")
Q2:Q4	Q2	=IF(R2<>"","Other","")
R2:R4	R2	=IFERROR(1/(1/(1-SUM(H2:P2))),"")

BUT, as you can see, the formulas are quite complicated. I suggest you update your user profile to include the version of Excel you're using. If you have Excel 365, those formulas might be simplified quite a bit. I picture an INDEX(SORT(COUNTIF(UNIQUE(FILTER kind of thing. But I don't have the new functions yet, so I can't assist there. Perhaps someone else will take a stab at it.

I also think that a different layout might help, but I haven't come up with anything better yet.

Overall, this feels like it would be much better handled with a macro. Let me know if that's of interest.

Good luck!

 

[ShanaVT]

Thank you! This works really well for a small set of data but for my large set it keeps crashing my excel. I would be interested in a macro that could work although I'm not very skilled using macros so I guess depends on how complicated it is to implement. Thank you again!

 

[Eric W]

Very true, on a big range, a complicated formula can easily crash your sheet. But using a macro isn't hard to do. If you'd like to try it, follow these steps.

Open a COPY of your workbook. Press Alt-F11 to open the VBA editor. Press Alt-IM to Insert a Module. In the window that opens, paste this code:

Sub Top5()
Dim ip As Range, op As Range, dat As Variant, dic As Object
Dim i As Long, r As Long, r2 As Long, t As Double, t2 As Double
Dim SortAry() As Variant, OArray() As Variant, x As Variant, y As Variant

    Set ip = Sheets("Sheet11").Range("A2")
    Set op = Sheets("Sheet11").Range("F1")
    
    dat = ip.Resize(ip.End(xlDown).Row - ip.Row + 1, 4).Value
    Set dic = CreateObject("Scripting.Dictionary")
    
    For i = 1 To UBound(dat)
        If dat(i, 2) = "Direct" Then
            If Not dic.exists(dat(i, 1)) Then dic.Add dat(i, 1), CreateObject("Scripting.Dictionary")
            dic(dat(i, 1))(dat(i, 3)) = dic(dat(i, 1))(dat(i, 3)) + dat(i, 4)
        End If
    Next i
    
    ReDim OArray(1 To dic.Count, 1 To 13)
    r = 0
    For Each x In dic
        r = r + 1
        OArray(r, 1) = x
        t = 0
        r2 = 0
        ReDim SortAry(1 To dic(x).Count, 1 To 2)
        For Each y In dic(x)
            t = t + dic(x)(y)
            r2 = r2 + 1
            SortAry(r2, 1) = y
            SortAry(r2, 2) = dic(x)(y)
        Next y
        Call SortIt(SortAry)
        t2 = t
        For c = 1 To IIf(UBound(SortAry) > 5, 5, UBound(SortAry))
            OArray(r, c * 2) = SortAry(c, 1)
            OArray(r, c * 2 + 1) = SortAry(c, 2) / t
            t2 = t2 - SortAry(c, 2)
        Next c
        If t2 > 0 Then
            OArray(r, 12) = "Other"
            OArray(r, 13) = t2 / t
        End If
    Next x
    
    op.Resize(, 13) = Array("ID", "Grouping 1", "", "Grouping 2", "", "Grouping 3", "", _
                           "Grouping 4", "", "Grouping 5", "", "Other", "")
    op.Offset(1).Resize(UBound(OArray), 13) = OArray
    
    op.Offset(1).Resize(UBound(OArray), 13).Sort Key1:=op.Offset(1)
        
End Sub
Sub SortIt(MyArray)
Dim i As Long, j As Long, p As String, a As Double

    For i = 1 To UBound(MyArray) - 1
        For j = 1 To UBound(MyArray) - i
            If MyArray(j, 2) < MyArray(j + 1, 2) Then
                p = MyArray(j, 1)
                a = MyArray(j, 2)
                MyArray(j, 1) = MyArray(j + 1, 1)
                MyArray(j, 2) = MyArray(j + 1, 2)
                MyArray(j + 1, 1) = p
                MyArray(j + 1, 2) = a
            End If
        Next j
    Next i
        
End Sub

Now look for these 2 lines near the top of the macro:

    Set ip = Sheets("Sheet11").Range("A2")
    Set op = Sheets("Sheet11").Range("F1")

Change the top one (ip) to be the top left corner of your source data, not including the headers. Then on the next line, change the range to be the top left corner of where you want the result table to go. Now press Alt-Q to close the editor.

Back in Excel, press Alt-F8 to open the macro selector. Select Top5 and click Run. This should run quite fast.

Let me know how it works!