Script to match column values and copy data-CodePudding

I am trying to copy-paste values from column W of sheet2 to column AK of sheet1 in case values of colA and colC of sheet1 and sheet2 respectively match

Data sample:

Sheet1

Sheet2

ColC ColW
A898 UK
C787
B487
A355 North America
O888
L151
A255 DACH
A717 UK

Result (Sheet1):

ColA    ColAK
A255    DACH
A355    North America
A717    UK
B487
A898    UK

Would you please advise on the best approach to do this? So far, I didn`t manage to come up with smth that is close to the desired result

CodePudding user response：

As what Cooper mentioned in the comments, your explanation does not match your sample data. But seeing your data, it makes more sense than the explanation above. So I will make use of the data given.

See script below:

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh1 = ss.getSheetByName('Sheet1');
  var sh1lastRow = sh1.getLastRow();
  // Sheet1!A:A values
  var sh1AValues = sh1.getRange(1, 1, sh1lastRow, 1).getValues().flat();
  // Sheet1!AK:AK range
  var sh1AKRange = sh1.getRange(1, 37, sh1lastRow, 1);

  var sh2 = ss.getSheetByName('Sheet2');
  var sh2lastRow = sh2.getLastRow();
  // Sheet2!C:C values
  var sh2CValues = sh2.getRange(1, 3, sh2lastRow, 1).getValues().flat();
  // Sheet2!W:W values
  var sh2WValues = sh2.getRange(1, 23, sh2lastRow, 1).getValues().flat();

  // for every values of Sheet1!A:A
  var output = sh1AValues.map(row => {
    // get position of every value in Sheet1!A:A from Sheet2!C:C
    // index is -1 if value is not found
    var index = sh2CValues.indexOf(row);
    // if position is not -1 (means it is found)
    if(index >= 0)
      // return the corresponding value in Sheet2!W:W
      return [sh2WValues[index]];
    // return blank if value in Sheet1!A:A is not found in Sheet2!C:C
    return [];
  });

  // set Sheet1!AK:AK range with value of generated array 
  sh1AKRange.setValues(output);
}

Sample data:

Output (Script):

If it is an option, this is also achievable by using VLOOKUP formula which should be simpler to do.

Formula:

=Arrayformula(if(isblank(A:A), "", VLOOKUP(A:A, {Sheet2!C:C, Sheet2!W:W}, 2, false)))

Sample data:

Output (Script):

Formula:

Output (Formula):