1

I have multiple values in a column and I wan to return the values which occurred 5 times. sample data:

      Date Column
Row1: July 1, 2019
Row2: July 1, 2019
Row3: July 1, 2019
Row4: July 1, 2019
Row5: July 1, 2019
Row6: July 5, 2019
Row7: July 5, 2019
Row8: July 5, 2019
Row9: July 5, 2019
Row10: July 5, 2019
Row11: July 10, 2019
Row12: July 12, 2019

I have tried some of the solutions I got from this site. One (Amit Agarwal) almost answered my question, unfortunately, his code was only to display the values with the maximum occurrence. Using my code and (mostly) his code, this is the result:

function countDate() {
  var ss = SpreadsheetApp.openByUrl(url);
  var ws = ss.getSheetByName("Test_Data");
  var dateRg = ws.getRange(1, 1, ws.getLastRow(), 1).getDisplayValues();
  var data = {}
  for (var i = 0; i < dateRg.length; i++) {
    data[dateRg[i]] = data[dateRg[i]] || 0;
    data[dateRg[i]]++;
  }
  var max = Object.keys(data).reduce(function(a, b) {
    return data[a] > data[b] ? a : b
  });
  ws.getRange(2, 4).setValue(max);
}

I want to get all the values that occurred 5 times from my list or column (google sheet). I honestly have no knowledge on how to do it. I'm so newbie in programming. I asked this question here already but no one is answering anymore, so I ended up asking it again and I really need a solution soon. I humbly ask anyone for help.

Rubén
  • 34,714
  • 9
  • 70
  • 166
GPni
  • 143
  • 1
  • 14
  • Do you want to get 1 instance of the values that occur 5 times? Or if it occurs five times display those 5 – xchrisbradley Jul 18 '19 at 14:11
  • Only one instance..in the example if july 1 and july 5 occurred 5 times i will have a display of July 1, 2019 and July 5, 2019. Thank you so much.. – GPni Jul 18 '19 at 14:15

1 Answers1

2

Here you go

var Date_Field = [
    'July 1, 2019',
    'July 1, 2019',
    'July 1, 2019',
    'July 1, 2019',
    'July 1, 2019',
    'July 5, 2019',
    'July 5, 2019',
    'July 5, 2019',
    'July 5, 2019',
    'July 5, 2019',
    'July 10, 2019',
    'July 12, 2019'
];
var CheckLimitReached = function (T) {
    var records = {};
    T.forEach(function (x) { records[x] = (records[x] || 0) + 1; });
    var limit_reached = Object.keys(records).filter(function (R) {
        return records[R] >= 5;
    });
    return limit_reached;
};
console.log(CheckLimitReached(Date_Field));
xchrisbradley
  • 416
  • 6
  • 21