Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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
share|improve this question
1  
Does the comma mean column separator or it is text inside the quoted string? –  neo Jan 31 '13 at 7:18
    
The comma means column separator. –  Dom Delimar Jan 31 '13 at 8:18
2  
I tweaked the formatting to make it look more like tabular data. –  Al E. Jan 31 '13 at 13:42
1  
@DomDelimar Did you had the time to check up on the answers given? –  Jacob Jan Tuinstra Mar 9 '13 at 20:36

2 Answers 2

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.

share|improve this answer
1  
Is there a way to do that in combination with the ARRAYFORMULA? –  Jacob Jan Tuinstra Jan 31 '13 at 8:55
    
this is a good advice but I am not familiar with ARRAYFORMULA. –  neo Jan 31 '13 at 9:02

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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