Google Sheets – How to Drag Autofill Multi-Column Pattern

google sheets

I'm trying to drag-copy some cells that are in the top row as headers. They get their values from a VLOOKUP formula (basicaly all group names are in column B and I want them as column headers). Before each cell there is another cell just containing string ID.

Table that I want drag autofill top row according to list in column B

What I want is to select first pair (ID and Machinery) and drag to the right so that this pattern continues for all group names in column B.

WA49425 second question example

It doesn't work. I thought selecting the first four cells (ID Machinery ID Clothing) and dragging them might help Google Spreadsheets recognize a pattern, but it didn't. I tried the same thing in Excel and if fails there too.

Then I realized dragging right only changes column letters in formulae not row numbers (which makes sense).

Is it possible to change this behaviour?

If not, any suggestions for me not to have to copy paste and edit for each entry?

Best Answer

As explained here when drag copy formula horizontally incrementation happens with anything related to columns. Nothing happens with rows. In order to get an increasing number one can use COLUMN() function in this scenario.

We get row number of original list with a calculation involving COLUMN() function, but then we have to create a string defining our original list's range and get the value in that cell. This is done by INDIRECT() function. (VLOOKUP method not good)

=INDIRECT("$B"&COLUMN()/2)

Working Formula in Spreadsheet for Horizontal Drag Auto-Fill

Now selecting ID and Machinery and dragging right fills all consecutive columns as desired.

Finished Spreadsheet, it WORKS

Another alternative:

=INDEX($B:$B;COLUMN()/2)

Code

function result(range) {
  var splitCol = 1; // split on column B
  var output2 = [];
  for(var i=0, iLen=range.length; i<iLen; i++) {
    var s = range[i][splitCol].split("\n");    
    for(var j=0, jLen=s.length; j<jLen; j++) {
      var output1 = []; 
      for(var k=0, kLen=range[0].length; k<kLen; k++) {
        if(k == splitCol) {
          output1.push(s[j]);
        } else {
          output1.push(range[i][k]);
        }
      }
      output2.push(output1);
    }    
  }
  return output2;
}

Explanation

The script evaluates each row, and particularly the second column of each row (in JavaScript arrays are zero based, so column 2 corresponds to index 1 of the array). It splits the contents of that cell, into multiple values and uses the "\n" as delimiter (line feed). After that it adds the existing info to an array and only add the individual results, when it hits index 1 (k == 1). The newly prepared row is then added to another array, that's being returned to show the result.

Screenshot

data
enter image description here

result
enter image description here

Example

I've created an example file for you: multi-line cells into new rows.

Add the script under Tools > Script editor.
Save the script, the name doesn't matter.
Use the custom function in a cell just like a built in (see the screenshot above).

Best Answer

Related Solutions

Google Sheets Average – How to Autofill the ‘Average’ Function in Google Sheets

Google Sheets – How to Split Multi-Line Cells into New Rows

Code

Explanation

Screenshot

Example

Related Topic