Split/Transpose one column into multiple rows and columns

0
votes

Created a Form for use with a videogame on Google Sheets, in order to track participation in group activities. I would like to have the data from Column C split and transposed across multiple columns, but am having trouble finding a solution that doesn't require me to manually expand the formula across the columns myself.

Here's an example of what I'm looking to do: Desired Result

From Column F and on, Row 1 is the timestamp, row 3 is the fleet commander, and rows 4 and on are the fleet members (it's fine if the fleet commander is there too, but brownie points if we can filter that individual out since they are also listed in row 3). This data will then be used to track each member's participation over time so they're rewarded for it.

So far I'm using =TRANSPOSE({Form!C2:C}) and following that up with =TRANSPOSE(SPLIT(D4,", ",0)), =TRANSPOSE(SPLIT(E4,", ",0)), etc. Like I said, though, I don't want to manually expand the cells because there will be a lot of fleets going out (including when I'm asleep) and the data needs to be updated ASAP.

2
splitgoogle-sheetstranspose
Hi, can you maybe share an example spreadsheet instead of a screenshot so that we have some data to work with ? - JPV
Sure, here's the link. What I need will be on the Engine tab. Will try the js option below tomorrow, but I'm hoping their's a formula solution. - dakaasin
Hi dakaasin, there is not much use in sharing a sheet if you don't allow copying. I would be glad to try to work out a formula, but please allow me to use the data (replace sensitive data with fake values if needed). - JPV
Apologies, old habbit preventing people from downloading/copying. I don't feel comfortable allowing anyone to edit the sheet directly, but those options are disabled now so you can download a copy of your own. - dakaasin

2 Answers

0
votes

For a formula based approach, try entering in E1

=ArrayFormula(transpose({A2:A,D2:D,B2:B, IFERROR(TRIM(REGEXEXTRACT(","&C2:C,"^"&REPT(",+[^,]+",COLUMN(OFFSET(A1,,,1,30))-1)&",+([^,]+)")))}))

If you don't really need the blank row you included in your desired result, change the above to

=ArrayFormula(transpose({A2:B,IFERROR(TRIM(REGEXEXTRACT(","&C2:C,"^"&REPT(",+[^,]+",COLUMN(OFFSET(A1,,,1,30))-1)&",+([^,]+)")))}))
0
votes

there may be a solution with direct spreadsheet formula (I'm thinking about tricks with arrayformula) but for the sake of simplicity I'll propose you a solution with a custom formula (a Google Apps Script). so here the demo and here the function used in the demo:

function fleetCommander(data) {
  var out = [[], []];
  var header = data.shift();
  var fleets = [];
  var maxFleet = 0;
  data.map(
    function (row) {
      if(row[0] == "" ) {return};
      var fleet = row[2].split(",");
      fleets.push(fleet);
      if(maxFleet < fleet.length) { maxFleet = fleet.length;}
      out[0].push(row[0]);
      out[1].push(row[1]);
    }
  );

  for(var fleetIndex = 0; fleetIndex < maxFleet; fleetIndex++) {
    out.push([]);
    }
  for(var fleetIndex = 0; fleetIndex < maxFleet; fleetIndex++) {
    for(var f = 0; f < fleets.length; f++) {
      out[fleetIndex + 2].push(fleets[f][fleetIndex] || "");
    }
  }
  return out;
}