I am writing a script for Google Spreadsheet.

Array 1 have a series of names (more than 50 different names)

John, Pete, Paul, ... Michael

Array 2 is a series of repeated names from those given 50 (more than 10K in total)

Paul, Michael, Pete, John, Paul, Paul, Pete, Michael, Paul, Michael,... Michael

How can I make another Array with the number of occurrences (Array 2) for every given name in Array 1?

Sorting is not possible. Therefore, Array 3 should take into consideration the order of Array 1. In this case, for instance:

1, 2, 3,... 4

I have seen & proved how to make it if order is not important (from Array 2, Array 1 is created with unique names and Array 3 contains their occurrences --> Counting the occurrences of JavaScript array elements), but all my approaches seem to don't work. So far, I have this:

var namesCountArray = [];
namesArray = ss.getRange("CU3:CU" + rows + "").getValues();
namesSerieArray = ss.getRange("DF3:DF" + rows + "").getValues();

for(var i=0; i< namesArray.length; i++) {
    var count = 0;
    for(var i = 0; i < namesSerieArray.length; ++i) {
        if(namesSerieArray[i] == namesArray[i])
            count++;
    }
    namesCountArray.push([count]);
}
ss.getRange("DB3").setValue(namesCountArray);

I am writing a script for Google Spreadsheet.

Array 1 have a series of names (more than 50 different names)

John, Pete, Paul, ... Michael

Array 2 is a series of repeated names from those given 50 (more than 10K in total)

Paul, Michael, Pete, John, Paul, Paul, Pete, Michael, Paul, Michael,... Michael

How can I make another Array with the number of occurrences (Array 2) for every given name in Array 1?

Sorting is not possible. Therefore, Array 3 should take into consideration the order of Array 1. In this case, for instance:

1, 2, 3,... 4

I have seen & proved how to make it if order is not important (from Array 2, Array 1 is created with unique names and Array 3 contains their occurrences --> Counting the occurrences of JavaScript array elements), but all my approaches seem to don't work. So far, I have this:

var namesCountArray = [];
namesArray = ss.getRange("CU3:CU" + rows + "").getValues();
namesSerieArray = ss.getRange("DF3:DF" + rows + "").getValues();

for(var i=0; i< namesArray.length; i++) {
    var count = 0;
    for(var i = 0; i < namesSerieArray.length; ++i) {
        if(namesSerieArray[i] == namesArray[i])
            count++;
    }
    namesCountArray.push([count]);
}
ss.getRange("DB3").setValue(namesCountArray);

• javascript
• arrays
• google-sheets

• do you really need to do this with a script? you may simply use a counta formula or for a more plicated analysis a pivot table? – Harold Commented Sep 30, 2014 at 15:46

3 Answers 3

You need to use a different variable in the second for loop or take it out all together:

for(var i=0; i< namesArray.length; i++) {
  var count = 0;
  for(var i = 0; i < namesSerieArray.length; ++i){
  if(namesSerieArray[i] == namesArray[i])
    count++;
  }

You're going through and checking each pair 1 with 1, 2 with 2, 3 with 3 only. You should do separate variables if you want to check each index pair (1 with 1, 1 with 2 ...). Ex:

for(var i=0; i< namesArray.length; i++) {
  var count = 0;
  for(var j = 0; j < namesSerieArray.length; j++){
  if(namesSerieArray[i] == namesArray[j])
    count++;
  }

// names we want to find
var names = ["john", "pete", "paul"];

// target list of names.
// john, pete, paul appear 2x
var target = ["john", "pete", "jack", "cindy", "thomas", "paul", "john", "pete", "paul"];

function finder(search, target) {
    return search.map(function (val) {
        return target.filter(function (e) {
            return val === e;
        }).length;
    });
}

finder(names, target);
// => [2, 2, 2]

Demo: http://jsfiddle/austinpray/v14o38ta/

Walk through the array2 and build an object with every key as a name and the value as the number of its occurences.

e.g.

var array2 = ["Paul", "Michael", "Pete", ... /* 47 more names */ ];
var occurences = {};

for (var i = 0, l = array2.length; i++; i<l) {
   if (occurences[array2[i]] === undefined) {
     occurences[array2[i]] = 1;
   }
   else {
     occurences[array2[i]]++;
   }
}

then walk the first array, check if the name is in the object occurences and push its value in a new array, like so

var array1 = ["Paul", "Michael", "Pete", "John", "Steve", "Bill" ];
var array1Frequency = [];

for (var i = 0, l = array1.length; i++; i<l) {
    array1Frequency.push(occurences[array1[i]] || 0)
}