9

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
0
5

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.

0
3

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 \

1

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

1

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

0

=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"))

0

0

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.