If this is the database

And this is the data exported from another system

What's the shortest way to create this report where the code is matched with the employee name and job?

The real database is huge and takes days to process manually every month.

Things I tried

1. Pivot tables. The problem is that it doesn't take data from multiple sheets
2. Combine the employee name with his code [Name1 - a123] on the system. The problem was that the system doesn't accept numbers in the employee name
3. Google for ways to combine tables. No resources found addressing my specific challenge.

Example sheet:

CodePudding user response：

Google Apps Script:

/**
* Merges the contents of the Database and System Export sheets into the Report sheet.
* 
* @customfunction
*/
function mergeSheets() {
  // Get the spreadsheet
  let spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  
  // Get the sheets you want to work with
  let database  = spreadsheet.getSheetByName('Database');
  let system    = spreadsheet.getSheetByName('System Export');
  if ((database || system) === null)
    throw new Error("1 or more requested sheets don't exist.");
  
  // Get sheet values
  let databaseValues = database.getRange('$A$2:$C')
    .getValues();
  let systemValues = system.getRange('$A$2:$C')
    .getValues();
  if ((databaseValues.length || systemValues.length) < 1)
    throw new Error('1 or more requested sheets are blank.');
  
  // Merge values
  let reportValues = [];
  for (let i in databaseValues)
    for (let j in systemValues)
      if (databaseValues[i][2] == systemValues[j][0])
        reportValues.push([
          databaseValues[i][0],
          databaseValues[i][1],
          databaseValues[i][2],
          systemValues[j][1],
          systemValues[j][2]
        ]);
  
  return reportValues;
}

You can paste this into the built-in script editor in Google Sheets. Use the formula =mergeSheets() inside the top-left (non-header) cell in the Report sheet and voila!