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

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 this screenshot.

I'm a beginner in JavaScript and any idea or structure on how I can do this using App Script will be very welcome.

arraysgoogle-sheetssumgoogle-sheets-formulagoogle-query-language


Solution 1:[1]

try:

=INDEX({QUERY({Sheet1!E2:E, Sheet1!AD2:AD, 
 COUNTIFS(Sheet1!E2:E, Sheet1!E2:E, ROW(Sheet1!E2:E), ">="&ROW(Sheet1!E2:E))}, 
 "select Col1,count(Col1) where Col1 is not null and Col3 <= 20 
  group by Col1 pivot Col2 label Col1'Name'"), 
 {"Total"; MMULT(QUERY(QUERY({Sheet1!E2:E, Sheet1!AD2:AD, 
 COUNTIFS(Sheet1!E2:E, Sheet1!E2:E, ROW(Sheet1!E2:E), ">="&ROW(Sheet1!E2:E))}, 
 "select count(Col1) where Col1 is not null and Col3 <= 20 group by Col1 
  pivot Col2"), "offset 1", 0)*1, {1;1;1})}})

enter image description here

Solution 2:[2]

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.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 player0
Solution 2

Related Questions