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

The picture below shows the initial input:

The picture below shows the problem when the stock changes:

Desired Output:

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:

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