Groupby include only counta < 4

[Dave Patton]

I have a list of students and courses that is about 20000 rows. I need a list of students with less that x courses.
I included a small example that shows a list and the expected results (see D16:E19)
E

T202408b.xlsx
	A	B	C	D	E
1	Student	Subject		Student	Student
2	ABC	Art		AA	2
3	ABC	English		ABC	5
4	ABC	Science		BBB	4
5	ABC	History		CAA	3
6	ABC	Math
7	BBB	Art
8	BBB	English
9	BBB	Science
10	BBB	History
11	CAA	Art
12	CAA	English
13	CAA	History
14	AA	Math
15	AA	Art
16				Less than 4
17				Student	Student
18				AA	2
19				CAA	3
20
Sheet3

Cell Formulas
Range	Formula
D1:E5	D1	=GROUPBY(A1:A15,A1:A15,COUNTA,3,0)
Dynamic array formulas.

 

[alansidman]

Power Query Solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Student"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each [Count] < 4)
in
    #"Filtered Rows"

 

[Artik]

=FILTER(D2:E5,E2:E5<4)

Artik

 

[Alex Blakenburg]

I don't have GroupBy yet on my MS365 build but wouldn't you just do this:
(Similar concept to Artik's version)

=LET(grp,GROUPBY(A1:A15,A1:A15,COUNTA,3,0),
            FILTER(grp,CHOOSECOLS(grp, 2)<4))

 

[Dave Patton]

Thanks very much Alex, Alan, and Artik.

 

[Alex Blakenburg]

You’re welcome.