Split content of one row into multiple rows?

Question

I have a table where each row contains data like this:

Team name | First member | Second member | Third member | Team description

Would it be possible to transform/break this data so that each row contains only one member? Like this:

Team name | First member  | Team description
Team name | Second member | Team description
Team name | Third member  | Team description

Answer 1

You can transform the columns by using TRANSPOSE, i.e., assume "First member, Second member, Third member" are A1:A3, you can use function in somewhere,

 = TRANSPOSE(A1:A3)

will fill column as below,

 First member
 Second member
 Third member

To repeat the column before and after team members, you can try,

 = TRANSPOSE (SPLIT(REPT(CONCAT(A1, ","), COUNTA(A1:A3)), ","))
 = TRANSPOSE (SPLIT(REPT(CONCAT(A5, ","), COUNTA(A1:A3)), ","))

which repeat the column with separater comma, then split and transform the result array.

Answer 2

Use the Google Sheets array handling features and FILTER to get the desired result

=FILTER({A:A,B:B,E:E;A:A,C:C,E:E;A:A,D:D,E:E},LEN({A:A;A:A;A:A}))

Note: If you spreadsheet use comma , as the decimal separator, replace the comma in the above formula by backslash \

Answer 3

If you use the following Google Apps Script, it will be done automatically:

function SPLIT() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();  
  var sh0 = ss.getSheets()[0], sh1 = ss.getSheets()[1];

  // get data from sheet 1
  var data = sh0.getDataRange().getValues();

  // create array to hold data
  var aMain = new Array();

  // itterate through data and add to array
  for(var i=1, dLen=data.length; i<dLen; i++) {
    for(var j=1; j<4; j++) {
      aMain.push([data[i][0],data[i][j],data[i][4]]);
    }
  }

  // add array of data to second sheet
  sh1.getRange(2, 1, aMain.length, 3).setValues(aMain);
}

I've prepared an example file: SPLIT. On opening, an extra menu item will be created with two options.

You need to tweak the script in order for it to work in your sheet. Add the script via the menu option Tools, Script Editor

Answer 4

To add to Rubén's great solution, the following modification to his formula will skip any blank users and group by teams:

=QUERY(FILTER({A:A,B:B,E:E;A:A,C:C,E:E;A:A,D:D,E:E},LEN({A:A;A:A;A:A})),"Select * Where Not Col2='' Order by Col1 Asc")

Answer 5

=ARRAYFORMULA(QUERY(TRIM(SPLIT(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
 IF(LEN(A1:A), "♠"&A1:A&"♦"&B1:D&"♦"&E1:E, )), , 999^99)), , 999^99), 
 "♠")), "♦")), "where Col2 is not null"))

Answer 6

I may have found a solution. Please check and let me know if it can be improved:

 function SPLIT() {
   var ss = SpreadsheetApp.getActiveSpreadsheet();  
   var sh0 = ss.getSheets()[0], sh1 = ss.getSheets()[1];

   // get data from sheet 1
   var data = sh0.getDataRange().getValues();

   // create array to hold data
   var aMain = new Array();

   // itterate through data and add to array
   for(var i=1, dLen=data.length; i<dLen; i++) {
     for(var j=5; j<data[i].length; j++) {
       if(data[i][j] != "") {
         aMain.push([data[i][0],data[i][1],data[i][2],data[i][4],data[i][j]]);
       } else {
         break;
       } 
     }
   }

   // add array of data to second sheet
   sh1.getRange(2, 1, aMain.length, 5).setValues(aMain);
 }

I'm still playing with it, but it changed this

Business Name	Phone	Website	Zip Codes	Membership	!	!	!	!	!
Phoo Test	4040404040	thtme.com	37075,37066,37072	RDS Member (Standard)	37075	37066	37072
Testing1	393939393	tht.com	33333,11111,22222,55555,77777	RDS Member (Standard)	33333	11111	22222	55555	77777
Test2	4949494	dmd.com	11199	RDS Member (Basic)	11199

To

Business Name	Business Phone	Website	Membership	Zip Code
Phoo Test	4040404040	thtme.com	RDS Member (Standard)	37075
Phoo Test	4040404040	thtme.com	RDS Member (Standard)	37066
Phoo Test	4040404040	thtme.com	RDS Member (Standard)	37072
Testing1	393939393	tht.com	RDS Member (Standard)	33333
Testing1	393939393	tht.com	RDS Member (Standard)	11111
Testing1	393939393	tht.com	RDS Member (Standard)	22222
Testing1	393939393	tht.com	RDS Member (Standard)	55555
Testing1	393939393	tht.com	RDS Member (Standard)	77777
Test2	4949494	dmd.com	RDS Member (Basic)	11199