0

I want to mirror column "A" and through a formula keep adding to the sequence in bold.

Is there a way to have it so I don't need to manually input the numbers in sequence?

Thanks.

Check sheet

iamthe202
  • 39
  • 6
  • I'm not sure what mean by "mirror" the column. Do you want to copy the content from column A to column C, and only change the bold numbers? Would the rest be the same? I can see column C has two times more content than column A, how would that work? Please consider clarifying your issue further. – Iamblichus Sep 14 '20 at 11:38
  • @lamblichus I wanna copy everything and only change the bold numbers; the rest would be the same. Yes, column C has more content, and that's what I intend to do: keep incrementing as many times as I want while having the numbers adding up. So I'd need a formula that generates the sequence of numbers every three rows. – iamthe202 Sep 14 '20 at 17:07
  • @calculuswhiz Column A is just a small part of what I want to have in Column C. The pattern is there, I just want to automatize it by incrementing to the sequence. So if in column A I have 1, x, y, z, 2, x, y, z, in column C I want 3, x, y, z, 4, x, y, z, and so on until I decide which number I want to stop on (be it 40, 400 or 4000). – iamthe202 Sep 14 '20 at 17:12
  • It can and already is. Column C is what I wanna have from column A by using a formula. In other words, how can a formula reproduce the same exact information in Column C based at Column A? – iamthe202 Sep 14 '20 at 20:58

1 Answers1

3

You could do this with an Apps Script Custom Function.

First, open a bound script by selecting Tools > Script editor, and copy the following functions to the script:

function MIRROR(until) {
  const sheet = SpreadsheetApp.getActive().getSheetByName("Question");
  const lastRow = getLastDataRow(sheet);
  const values = sheet.getRange(1, 1, lastRow).getValues();
  let series = values.slice(0, 4);
  let output = [];
  let currentNumber = values[values.length - 2][0] + 1;
  if (!until) currentNumber;
  for (; currentNumber <= until; currentNumber++) {
    output.push([[currentNumber],[series[1][0]],[""],[""]]);
  }
  return output.flat();
}

function getLastDataRow(sheet) { // Get last row in column A (see https://stackoverflow.com/a/53587462)
  var lastRow = sheet.getLastRow();
  var range = sheet.getRange("A" + lastRow);
  if (range.getValue() !== "") {
    return lastRow;
  } else {
    return range.getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
  }
}

Once it is defined, you can use the function MIRROR the same you would any sheets built-in function. This function would accept a parameter in which you would specify until what number should the series continue. See, for example, this:

enter image description here

Reference:

Iamblichus
  • 18,540
  • 2
  • 11
  • 27