Google-sheets – Merge array into column row by row

google sheets

I'm trying to do this without code.

I have an array of 4 columns with an undefined amount of rows, and I want to merge them into 1 column, but not by appending columns (so I can't use {A1:A;B1:C1;C;..}), but rather by appending cell, line by line.

Example:

Input:

Val1a Val1b Val1c Val1d
Val2a Val2b Val2c Val2d
Val3a Val3b Val3c Val3d
Val4a Val4b Val4c Val4d
Val5a Val5b Val5c Val5d
...

Output:

Val1a
Val1b
Val1c
Val1d
Val2a
Val2b
Val2c
Val2d
Val3a
...

Best Answer

Julien, let's say your sample data above were in the range A1:D5. Use this:

=TRANSPOSE(SPLIT(TEXTJOIN("/",TRUE,A1:D5),"/"))

This assumes that your data itself does not contain a forward slash, which in the formula, is being used as a temporary delimiter. If, for some reason, your data does contain a slash (e.g., URLs, etc.), just replace the two instances of the forward slash in the formula with any symbol or combination of symbols that your data does not include.

How it works:

TEXTJOIN will string together the whole range into one long text string, running left to right per row, separating everything by a delimiter of choice.
SPLIT will then split that text string again at those delimiters, which will make a long horizontal array of everything in the original range.
TRANSPOSE will take that long horizontal array and change it to a vertical array.

If you then want to keep the hard data created by the formula:

Select the results range.
Hit Ctrl-C to copy it to clipboard.
With the range still selected, hit Ctrl-Alt-V to Paste Special.
A small clipboard icon will appear at the lower right corner of the range. Click it and choose "Paste values only."

Related Solutions

Google-sheets – Creating a function that runs when a Google spreadsheet is opened

This little script will:

create an extra menu item, thus making it accessible throughout the workbook. T
the menu item will set the active range to column E, last row + 1.

See the example I've prepared: Creating a function...Google spreadsheet is opened (edit rights)

function onOpen() {
  // get active spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // create menu
  var menu = [{name: "get last row", functionName: "getLastRow"}];

  // add to menu
  ss.addMenu("Rows", menu);  

  // execute function
  getLastRow();  
}

function getLastRow() {
  // get active spreadsheet
  var ss = SpreadsheetApp.getActive();

  // get active sheet
  var sh = ss.getActiveSheet();

  // get column F
  var indLastCol = sh.getLastColumn()-1;

  // get last row + 1
  var indRowWithout = sh.getLastRow()+1;

  // set new active range
  sh.setActiveCell('E'+indRowWithout);
}

Google Sheets – Merging Column Headings Based on Specific Values

Try this formula for cell H2:

=JOIN(", ", FILTER(B$1:E$1, B2:E2 = 0))

This filters the header row (B1:E1) on values from B2:E2 which has the value 0. The resulting strings are joined with ,.

You can copy this formula to the other cells in column H by dragging it across. The B2:E2 will adjust automatically to match the other rows.

Feel free to look at and copy the example spreadsheet I've set up.

Best Answer

Related Solutions

Google-sheets – Creating a function that runs when a Google spreadsheet is opened

Google Sheets – Merging Column Headings Based on Specific Values

Related Topic