Google-sheets – Transposing varied number of columns per entry into duplicate entries with one column in Google Sheets

google sheets

I feel almost certain this has been answered, but in a time crunch I am hoping someone can outline the solution for me, as my searches returned variations that were not quite what I wanted.

I have data that is arranged with multiple columns per entry:

I would like to transpose the columns data into rows duplicating the first two columns as indices:

Much appreciation for any solutions.

Best Answer

As you have noted, there is a precedent for this on either StackOverflow or webapps but I couldn't find it, so I've recreated an equivalent solution. I'm pretty sure that this formula can be simplified BUT it does work - so simplification is for another day/user.

Use this formula (assumes that the raw data is in sheet="Data"):

=query({{"Number","Name","Code"};sort(query({query({query({query({query(Data!A2:B6) ,query(Data!A2:F6) },"select Col1, Col2, Col5 where Col1 = Col3");query({query(Data!A2:B6) ,query(Data!A2:F6) },"select Col1, Col2, Col6 where Col1 = Col3 and Col6 is not null")});query({query(Data!A2:B6) ,query(Data!A2:F6) },"select Col1, Col2, Col7 where Col1 = Col3 and Col7 is not null")});query({query(Data!A2:B6) ,query(Data!A2:F6) },"select Col1, Col2, Col8 where Col1 = Col3 and Col8 is not null")}),2,true)})

The logic is

create four(4) x two-array queries: the first part is Columns A & B (Col1 and Col2), the second part is Columns A to F (Col3 to Col8)
- each query lists Col1 and Col2 and one of Col5 to Col 8 respectively.
- these quesries are then stacked. Note that are joined between curly brackets and separated by a semicolon {;}
the end result is then sorted using SORT
the last step is to add the column headings (though this is optional) by creating another two-array query

BEFORE

AFTER

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).

Google Sheets Concatenate – How to Concatenate Several Columns into One

You need to filter down to non-empty cells, and then stack the results together:

={filter(A:A, len(A:A)); filter(B:B, len(B:B)); filter(C:C, len(C:C))}

This is the same as this answer by grayob which is unfortunately buried under outdated answers.

Best Answer

Related Solutions

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

Code

Explanation

Screenshot

Example

Google Sheets Concatenate – How to Concatenate Several Columns into One

Related Topic