How to retrieve Top 2 rows from each group in excel sheet?-CodePudding

The problem is simple to understand. I just need to know a formula that will help find a way to fetch the top 2 rows of each group in an excel sheet.

The below example is grouped by column 1.

Example

Given Table:

Column 1	Column 2
Apple	A102012
Apple	A102013
Apple	A102014
Banana	A102015
Banana	A102016
Banana	A102017
Coconut	A102017

Result:

Column 1	Column 2
Apple	A102012
Apple	A102013
Banana	A102015
Banana	A102016
Coconut	A102017

CodePudding user response：

Try:

Formula in D1:

=REDUCE(A1:B1,UNIQUE(A2:A8),LAMBDA(a,b,VSTACK(a,TAKE(FILTER(A2:B8,A2:A8=b),2))))

To mimic this for Google Sheets:

=REDUCE(A1:B1,UNIQUE(A2:A8),LAMBDA(a,b,{a;QUERY(A2:B8,"Where A='"&b&"' limit 2")}))

A much slower alternative is:

=FILTER(A:B,INDEX(COUNTIFS(A:A,A:A,ROW(A:A),"<="&ROW(A:A)))<3)

CodePudding user response：

If you have Excel 365 you can also use this formula

=LET(rank,MAP(tblData[value],tblData[fruit],
          LAMBDA(v,f,SUMPRODUCT((tblData[fruit]=f)*(v<tblData[value])) 1)),
FILTER(tblData,rank<=2))

The MAP function calculates the rank of each row within its group. Then we can filter by that list.

CodePudding user response：

A faster method than countifs not using most recent additions to Excel, if it can be assumed that data are pre-sorted:

=LET(count,COUNTA(A:A),Column1,A2:INDEX(A:A,count),Column12,A2:INDEX(B:B,count),FILTER(Column12,SCAN(0,SEQUENCE(count-1),LAMBDA(a,c,IF(c=1,1,IF(INDEX(Column1,c)=INDEX(Column1,c-1),a 1,1))))<=2))