Home > Software engineering >  Google Sheets - shift the cell value according to the name beside the cell
Google Sheets - shift the cell value according to the name beside the cell

Time:06-16

Problem: The details of item can't move together when latest stock update.

The picture below shows the initial input:

enter image description here

The picture below shows the problem when the stock changes:

enter image description here

Desired Output:

enter image description here

The formula of Name column for repeat the item:

=QUERY(ArrayFormula(FLATTEN(SPLIT(REPT($AL11:$AL13&"@",$AM11:$AM13),"@"))),"where Col1 is not null")

CodePudding user response:

Suggestion

Using apps script

Not sure if it is possible using formula, but here's a script you can use. This of course still needs some tweaking to align with your spreadsheet.

Also what I've done with the script is it deletes both the last name and detail if you change it to lower stock since it makes more sense rather than keeping it in same spot.

try:

function onEdit(e) {
  const ss = e.source;
  const range = e.range;
  if(range.getColumn() == 2 && range.getRow() > 1) {
    const sheet = ss.getActiveSheet();
    const lastRow = sheet.getLastRow();
    const dataRange = sheet.getRange(2, 1, lastRow - 1, 2);
    const dataValues = removeEmptyRows(dataRange.getValues());
    const outputRange = dataRange.offset(0, 2);
    const outputData = removeEmptyRows(outputRange.getValues());

    let output = [];

    dataValues.filter(x => x[1] && x[1] > 0).forEach(row => {
      const [fruit, count] = row;
      let currentFruit = outputData.filter(x => x[0] == fruit);
      let currentCount = currentFruit.length, rows;

      if(currentCount < count) {
        rows = count - currentCount;
        currentFruit.push(...Array(rows).fill([fruit, '']));
      }
      else if(count < currentCount) {
        rows = currentCount - count;
        currentFruit = currentFruit.slice(0, -1 * rows);
      }
      output.push(...currentFruit);
    });

    outputRange.clearContent();
    sheet.getRange(2, 3, output.length, output[0].length).setValues(output);
  }
}

function removeEmptyRows(array) {
  return array.filter(x => x.filter(String).length > 0)
}

Result:

enter image description here

Let me know if this works or if you have other questions.

  • Related