How to filter data in a Google Sheet so that just the last 20 items per name are included?-CodePudding

I have a google spreadsheet that have more then 1000 rows and it gets updated weekly, each row contain name and one of 3 decisions was made. I want to automate it and have in a separate sheet a table with names and count decisions in numbers and just last 20 for each name.

The desired result would look like

CodePudding user response：

You can create descending ordinals by agent with this formula in cell Sheet1!AF1:

=arrayformula( 
  { 
    "Descending ordinal by agent"; 
    iferror( 1 / 
      countifs( 
        E2:E, E2:E, 
        row(E2:E), ">=" & row(E2:E) 
      ) 
    ^ -1 ) 
  } 
)

Then Insert > Sheet and insert this formula in cell A1 of the new sheet:

=query( 
  Sheet1!A1:AF, 
  "select E, count(E) 
   where AD is not null 
   and AF <= 20 
   group by E 
   pivot AD", 
  1 
)

The formula will give counts by name and decision, only looking at rows where the ordinal is 20 or less. See the new Solution sheet in your sample spreadsheet.