CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-formula
bvboisseree
bvboisseree

Reputation: 3

Google Spreadsheets ArrayFormula: How to split and transpose a cell-range?

Hello everybody and thanks a lot for your help. Here's my problem:

What I have: I have a table with raw data in 53 rows and numerous columns which I would like to reduce and restructure into three columns: City, Date and Value. https://docs.google.com/spreadsheets/d/1bsdC8lrtSGk957ae8Z0VRGnDqTZfFLPpLkfoid0UbIQ/edit?usp=sharing What I've done so far: For a single row, I used the following formula to make everything work as I wanted it to:

ArrayFormula({SPLIT(TRANSPOSE(Base_Data!A2)&"|"&TRANSPOSE(Base_Data!AJ1:1&"|"&Base_Data!AJ2:2),"|")})

What I want: I'd like to extend the formula to work for the entire area, all 53 rows. Does anyone have a tip for this? The solution doesn't have to be a formula, it would work as a script, too

Upvotes: 0

Views: 1417

Answers (2)

Erik Tyler
Erik Tyler

Reputation: 9345

I've set up a new sheet called "New_Data [Erik]" and placed the following formula into A2:

=ArrayFormula(SPLIT(FLATTEN(Base_Data!A2:A&"\"&Base_Data!AJ1:1&"\"&Base_Data!AJ2:54),"\",0,1))

If this is a one-time conversion, I'd recommend copying the results in place. To do that, select A:C, hit Ctrl-C to Copy and then Ctrl-Alt-V to Paste Special. A small clipboard icon will appear. Click it and choose "Paste Values Only."

If you'll need this functionality ongoing, just understand that FLATTEN is a not-yet-official function of Google Sheets, which means that while Google sheets may very well make it official, they may also decide to do away with it at any time. (This is why I suggest copying and pasting the results in place, if it's just a one-time conversion.)

Upvotes: 1

Dmitry Kostyuk
Dmitry Kostyuk

Reputation: 1459

Not sure what you're trying to get to there. If you are trying to leave out all columns but 3, just do ={Base_Data!A2:A, Base_Data!E2:E} and add as many columns as you require comma-separated within the curly brackets

Upvotes: 0

Related Questions