I would like to pull data from Sheet1 to Sheet2 based on a criteria and a cell value where x rows are only copied when [Qualification] = 'Qualified' and, if [No of positions] = x. If x= 1, specific columns are copied once to Sheet 2. If x = 2, specific columns are copied twice to Sheet 2.

Below is an example.

Sheet1

Sheet2

I am currently running this query

function RepeatMatching(values, criterion = 'Qualified') {
  return values.flatMap(([, b, c, , e, f]) =>
    e > 0 && f == criterion ? [...Array(e)].fill([b, c, f]) : []
  );
}
;  

And run =RepeatMatching(Sheet1!A1:F, "Qualified") in Sheet2

However, when [No of Positions] = x changes from 2 to 1, there is a mismatch with the Owner. Is it possible to delete a whole row or add a whole row when there is a change in x?

CodePudding user response：

To dynamically output [Owner] in Sheet2, you need to manage the master data of Owner (maybe [Lead ID] and [Owner]) in a separate sheet and do ARRAYFORMULA(VLOOKUP(...)) in it.

Sheet3

Formula to put into D2 of Sheet2:

=ARRAYFORMULA(IF(A2:A="",,VLOOKUP(A2:A,'Sheet3!A:B',2,FALSE)))

CodePudding user response：

Issue:

I don't think what you want to do is possible. Mixing data returned via formula with data entered manually is not a good idea.

Solution:

Nevertheless, I think this could be accomplished by using an onEdit trigger. The onEdit function would do the following:

• Grab the source data from Sheet1 whenever this data is manually edited.
• Get the current data in Sheet2, including Owner (in the sample above, this data is located on columns A:E).
• For each Qualified row in the source data, retrieve the Owners that already exist on Sheet2 with the same Lead ID (I'm assuming here that Lead ID can be used to identify a source row).
• For each Qualified row in the source data, append a number of rows equal to No of positions, each with the corresponding Owner, if it exists.
• Clear the old range and copy the new values, using clearContent and setValues.

Code sample:

function onEdit(e) {
  const range = e.range;
  const sheet = range.getSheet();
  if (sheet.getName() === "Sheet1" && range.getColumn() < 7 && range.getRow() > 1) { // Check edited range is source data
    const sourceValues = sheet.getRange("A2:F"   sheet.getLastRow()).getValues();
    const ss = e.source;
    const targetSheet = ss.getSheetByName("Sheet2");
    const targetLastRow = targetSheet.getLastRow();
    let oldTargetValues = [];
    let oldTargetRange;
    if (targetLastRow > 1) {
      oldTargetRange = targetSheet.getRange("A2:E"   targetSheet.getLastRow());
      oldTargetValues = oldTargetRange.getValues(); // Get current data in Sheet2
    }
    const targetValues = sourceValues.reduce((acc, [, sourceLeadId, companyName, , numPositions, qualification]) => { // Iterate through source data to build new target data
      if (qualification === "Qualified") { // Grab only Qualified rows
        const currentOwners = oldTargetValues.filter(row => row[0] === sourceLeadId).map(row => row[3]); // Get owners related to current Lead ID
        for (let i = 0; i < numPositions; i  ) { // Append source data "No of position" times
          const targetRow = [sourceLeadId, companyName, qualification];
          if (currentOwners[i]) targetRow.push(currentOwners[i]);
          else targetRow.push("");
          acc.push(targetRow);
        }
      }
      return acc;
    }, []);
    if (oldTargetRange) oldTargetRange.clearContent(); // Clear old target range
    targetSheet.getRange(2,1,targetValues.length, targetValues[0].length).setValues(targetValues); // Write new target data
  }
}