Copy column A3:A9-E3:A9: to A,C,E,G, I in an other sheet

Question

Is it possible to copy from column A3:A9-E3:A9: and then target them to A,C,E,G,I in an other sheet?

Example

Answer 1

use this formula:

=ARRAYFORMULA(SPLIT(TRANSPOSE(QUERY(TRANSPOSE("♦"&A3:E9&"♦");;999^99)); "♦"))

then copy with CTRL+C and re-paste/paste over with CTRL+SHIFT+V

Answer 2

I think your best option, if you want to be able to use the columns in between, is using Google Apps Script. I don't think it is possible to do this with a sheets built-in function or even with an Apps Script custom function.

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

function copyRange() {
  // Retrieve original range:
  var inputRange = "'Ark1'!A3:E9"; // Range of original columns (please change this according to your preferences)
  var inputValues = SpreadsheetApp.getActive().getRange(inputRange).getValues();
  // Transpose array so that outer array corresponds to columns:
  var transposedValues = inputValues[0].map(function(column, i) {
    return inputValues.map(function(row) {
      return row[i];
    });
  });
  // Get selected range (this will be the destination range):
  var outputRange = SpreadsheetApp.getActiveRange();
  var targetSheet = outputRange.getSheet();
  var row = outputRange.getRow();
  var col = outputRange.getColumn();
  // Copy the source range to the destination (ignoring each second column):
  transposedValues.forEach(function(column, j) {
    for (var i = 0; i < column.length; i++) {
      targetSheet.getRange(row + i, col + j * 2).setValue(column[i]);
    };
  });
}'

This script does the following:

• Retrieves the values in a certain range, defined in the first line of this sample, via getRange and getValues.
• "Transposes" the retrieved 2D array corresponding to the values of the source range, so that each element in the outer array corresponds to a column, which will make the writing process easier, as described in this answer.
• Retrieve the currently selected range via getActiveRange
• Copy the source range to the currently selected range with setValues.

The problem with this approach, of course, is that it's more uncomfortable to run this kind of functions than a built-in or a custom function. You could attach this function to an image or to a menu to make the workflow less painful. But I cannot think of a better solution.

Crucially, you would have to change the reference of the input range (in this sample 'Ark1'!A3:E9).

Reference:

• Sheet.getRange
• Range.getValues
• SpreadsheetApp.getActiveRange)
• Range.setValue

I hope this is of any help.