How would I duplicate a sheet to only show an updated list of the 'last 10 items logged by each user'

Ask for Help
2

Hi Jaime, this was a bit of a tricky one.

I’m used to group-wise ranking in SQL but have never tried this in Google Sheets, interesting use case.

For this one I have 2 steps:

  • Ranking group-wise by a script (as Arrayformula, Rank and If can’t work together in my experience).

  • Query to return only rankings less than or equal a pre-defined number.

image

In this example I tried to return 3 latest entries for each type of user (A & B).

The script I used for the custom function:

  function customrank(arr) {
  arr = arr.filter(function(r) {
    return r[0] != "";
  });
  return arr.map(function(r1) {
    return arr.reduce(function(rank, r2) {
      return rank += (r2[0] == r1[0] && r2[1] > r1[1]);
    }, 1);
  });
}

The link to the Sheet is [here](https://docs.google.com/spreadsheets/d/1sEQHpSKyHx9w2F_Xx3lm56SU1p-8eI81OYU5TYvQFvw/edit#gid=119936354.

1 Like