Compare strings, coefficients

Question

I currently have a sheet with multiple parameters tied to multiple coefficients. I have parameter in left side and parameter in the right side.

How to make so that on the SAME LINE, the coefficients of the same parameter appear (the parameters of the right side will then be the same as that of the left side on the same line). If a parameter on the left does not appear on the right, then leave a blank space on the right. I also have, for certain parameters, several coefficients.

enter image description here

Answer 1

Description

The following example uses the coefficients in column A and filter out all matching coefficients from column E:G. It also arranges them into the same row.

Script

function test() {
  try {
    let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Test");
    let sheetLastRow = sheet.getLastRow();
    // Get range of A:C
    let coef1 = sheet.getRange(1,1,sheetLastRow,3);
    let lastRow = coef1.getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
    coef1 = coef1.getValues();
    if( lastRow < sheetLastRow ) coef1 = coef1.slice(0,lastRow);
    // Put headers in result array
    let result = [coef1.shift()];
    // Get range of E:G
    let coef2 = sheet.getRange(1,5,sheetLastRow,3);
    lastRow = coef2.getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
    coef2 = coef2.getValues();
    if( lastRow < sheetLastRow ) coef2 = coef2.slice(0,lastRow);
    // Remove headers
    coef2.shift();
    // Find unique coef numbers
    let coef = [...new Set( coef1.map( row => row[0] ) )];
    // Filter all elements from E:G matching A:C
    coef.forEach( c => {
      let match = coef2.filter( row => row[0] === c );
      if( match.length === 0 ) {
        result.push(['','','']);
      }
      else {
        result = result.concat(match);
      }
    });
    //  For now put in I:K
    sheet.getRange(1,9,result.length, result[0].length).setValues(result)
  }
  catch(err) {
    console.log(err);
  }
}

Screen shot

Reference

• Range.getNextDataCell()
• Set
• Array.slice()
• Array.shift()
• Array.map()
• Array.forEach()
• Array.filter()
• Array.concat()