7

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
  • 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. – ale 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
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.

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 \

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

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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