Google-sheets – How to duplicate two cells from a column to rows in a particular order

google sheets

I would like to duplicate the cells from a column in a certain order. The cells in the column are dates.
As an example:

My goal is to take the first date cell, copy it into another row in the same spreadsheet and then take the second date cell and copy it next to the first copied cell. Then duplicate the second cell (from the first row) into the first cell of the second row and then take third date cell and put it to the second cell of the second row. The result should look like this:

        Column 1    Column 2
Row A   2014-12-16  2014-12-23
Row B   2014-12-23  2015-1-23
Row C   2015-1-23   2015-2-03

So the next rows (D and E) in this case should be:

2015-2-03   2015-2-18
2015-2-18   2015-3-11

I have a lot of cells so I am looking for some efficient way on how to do it.

What I have tried:
Trans copy (selected cells are copied into rows from the column). But that does not solve my problem.

Best Answer

This can be done with a single array formula such as

={A1:A5, A2:A6}

(assuming the dates you've shown are in the range A1:A6).

If you expect more dates to be added, and don't want to specify the last row, then two separate arrayformulas should be used for two columns: ={A1:A} and ={A2:A}.

Code

function myFind() {
  var ss = SpreadsheetApp.getActive(), output = [];
  var findData = ss.getSheetByName('fr').getDataRange().getValues();
  var searchData = ss.getSheetByName('cql').getDataRange().getValues();

  for(var i=1, iLen=findData.length; i<iLen; i++) {  
    for(var j=0, jLen=searchData.length; j<jLen; j++) {
      for(var k=0, kLen=searchData[0].length; k<kLen; k++) {
        var find = findData[i][0];
        if(find == searchData[j][k]) {
          output.push([find, "row "+(j+1)+"; "+"col "+(k+1)]);
        }
      }
    }
  }
  return output;
}

Explained

Both data ranges are "captured" at once via the .getDataRange.getValues() method. The 2d array, that's being returned, includes empty rows and columns. This means that all we need to do is correct for the zero based array and we have a row and column index. Through the iterations (note the var i=1 to skip the header), the result is being pushed into an output array. Finally, the output is returned.

Screenshot

find
enter image description here

search enter image description here

result
enter image description here

Example

I've created an example file for you: get cell row and column index

Best Answer

Related Solutions

Google-sheets – How to Fill a Column with Sequential Dates in Google Sheets

Google-sheets – Fetch cell row and column indices in Google Sheets

Code

Explained

Screenshot

Example

Related Topic