CODEWITHSUNDEEP
regexgoogle-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-query
Calum Fidalgo
Calum Fidalgo

Reputation: 13

Google Sheets Search and Sum in two lists

I have a Google Sheets question I was hoping someone could help with.

I have a list of about 200 keywords which looks like the ones below:

**List 1** 
Italy City trip
Italy Roundtrip
Italy Holiday
Hungary City trip
Czechia City trip
Croatia Montenegro Roundtrip
....
....

And I then have another list with jumbled keywords with around 1 million rows. The keywords in this list don't exactly match with the first list. What I need to do is search for the keywords in list 1 (above) in list 2 (below) and sum all corresponding cost values. As you can see in the list below the keywords from list 1 are in the second list but with other keywords around them. For example, I need a formula that will search for "Italy City trip" from list 1, in list 2 and sum the cost when that keyword occurs. In this case, it would be 6 total. Adding the cost of "Italy City trip April" and "Italy City trip June" together.

**List 2**                   Cost
Italy City trip April         1
Italy City trip June          5 
Next week Italy Roundtrip     4
Italy Holiday next week       1
Hungary City holiday trip     9
....
....

I hope that makes sense.

Any help would be greatly appreciated

Upvotes: 1

Views: 186

Answers (3)

Kevkeev13
Kevkeev13

Reputation: 389

I also got this one, but it's case sensitive a bit 

function myFunction() {
  var ss = SpreadsheetApp.getActive();
  var sheet1 = ss.getSheets()[0];
  var sheet2 = ss.getSheets()[1];

  var valuesSheet1 = sheet1.getRange(2,1, (sheet1.getLastRow()-1), sheet1.getLastColumn()).getValues();
  var valuesCol1Sheet1 = valuesSheet1.map(function(r){return r[0]});
  var valuesCol2Sheet1 = valuesSheet1.map(function(r){return r[1]});
  Logger.log(valuesCol2Sheet1);

  var valuesSheet2 = sheet2.getRange(2,1, (sheet2.getLastRow()-1)).getValues();
  var valuesCol1Sheet2 = valuesSheet2.map(function(r){return r[0]});

  for (var i = 0; i<= valuesCol1Sheet2.length-1; i++){
 var price = 0;
 valuesCol1Sheet1.forEach(function(elt,index){
  var position = elt.toLowerCase().indexOf(valuesCol1Sheet2[i].toLowerCase());
     if(position >-1){
  price = price + valuesCol2Sheet1[index];
  }
  });
  sheet2.getRange((i+2),2).setValue(price);
  };
  }

Upvotes: 1

Tedinoz
Tedinoz

Reputation: 7984

You want to establish whether keywords in one list (List#1) can be found in another list (List#2).

List#2 is 1,000,000 rows long, so I would recommend segmenting the list so that execution times are not exceeded. That's something you will be able to establish by trial and error.

The solution is to use the javascript method indexOf.

Paraphrasing from w3schools: indexOf() returns the position of the first occurrence of a specified value in a string. If the value is not found, it returns -1. So testing if (idx !=-1){ will only return List#1 values that were found in List#2. Note: The indexOf() method is case sensitive.

 function so5864274503() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var srcname = "source";
  var tgtname = "target";
  var sourceSheet = ss.getSheetByName(srcname);
  var targetSheet = ss.getSheetByName(tgtname);

  // get the source list 
  var sourceLR = sourceSheet.getLastRow();
  var srcData = sourceSheet.getRange(1,1,sourceLR).getValues();

  //get the target list
  var targetLR = targetSheet.getLastRow();
  var tgtlist = targetSheet.getRange(1,1,targetLR,2).getValues();
  var totalcostvalues = [];

  //  start looping through the keywords (list 1)
  for (var s = 0;s<srcData.length;s++){
    var totalcost = 0;
    var value = srcData[s][0]

    // start looping through the strings (List 2)
    for (var i=0;i<tgtlist.length;i++){
      // set cost to zero
      var cumcost = 0;
      // use indexOf to test if keyword is in the string
      var idx = tgtlist[i][0].indexOf(value);

      // value of -1 = no match, value >-1 indicates posuton in the string where the key word was found      
      if (idx !=-1){
        var cost = tgtlist[i][1]
        cumcost = cumcost + cost;
        totalcost = totalcost+cost
      }

    }//end of loop - list2


    //Logger.log("DEBUG: Summary: "+value+", totalcost = "+totalcost)
    totalcostvalues.push([totalcost])

  }// end of loop - list1

  //Logger.log(totalcostvalues);  //DEBUG
  sourceSheet.getRange(1,2,sourceLR).setValues(totalcostvalues);
}

Upvotes: 1

player0
player0

Reputation: 1

try:

=ARRAYFORMULA(QUERY({IFNA(REGEXEXTRACT(PROPER(C1:C), 
 TEXTJOIN("|", 1, SORT(PROPER(A1:A), 1, 0)))), D1:D}, 
 "select Col1,sum(Col2) 
  where Col1 is not null 
  group by Col1 
  label sum(Col2)''", 0))

0

Upvotes: 2

Related Questions