In Google Sheets, how to paste a series of cells in a row vertically and break and go to the next ro-CodePudding

As you can see from the attached sheet, I need to evaluate cells B2:K5 in "Internal Split" and paste cells that have values into Column A of sheet "Accounting Split". When it finds an empy cell (example: Cell D2), it should break and start looking in the next row for cells with values until it pastes all values vertically in column A of "Accounting Split". Thanks in advance

CodePudding user response：

function pivot() {
  const ss = SpreadsheetApp.getActive();
  const sh1 = ss.getSheetByName("Internal Split");
  const vs1 = sh1.getRange(2,1 , sh1.getLastRow() - 1, 11).getValues();
  const sh2 = ss.getSheetByName("Accounting Split");
  let o = vs1.map(r => {
    return r.slice(1).filter(e => e).map(e => [r[0],e])
  }).flat();
  //Logger.log(JSON.stringify(o))
  o.unshift(["Country","Cost"]);
  sh2.clear();
  sh2.getRange(1,1,o.length,o[0].length).setValues(o);
}

Data:

	A	B	C	D	E	F	G	H	I	J	K
1	Delivery	Cost 1	Cost 2	Cost 3	Cost 4	Cost 5	Cost 6	Cost 7	Cost 8	Cost 9	Cost 10
2	South Africa	500	300
3	China	400	200	700	800
4	USA	100
5	Brazil	800	670	450

Output:

	A	B
1	Country	Cost
2	South Africa	500
3	South Africa	300
4	China	400
5	China	200
6	China	700
7	China	800
8	USA	100
9	Brazil	800
10	Brazil	670
11	Brazil	450

CodePudding user response：

I'm not much of a coder so I was able to achieve the desired result with formulas on Google Sheets

Here is the formula:

=TRANSPOSE(SPLIT(TEXTJOIN(",",TRUE,'Internal Split'!C2:Q2,'Internal Split'!C3:Q3,'Internal Split'!C4:Q4,'Internal Split'!C5:Q5,'Internal Split'!C6:Q6),","))

What this formula does is first go through a range of cells and return cells with values separated with a comma. The output is in one cell. It then splits this cell into multiple values. Finally it transposes this result to display the results vertically. I hope that this is explained clearly.