Splitting rows into ranges

Question

I have a google sheet with data that looks like this:

StudentA  Module1  Grade1  Module2  Grade2
StudentB  Module1  Grade1  Module2  Grade2

i want to split it using a formula to look like this:

StudentA Module1 Grade1
StudentA Module2 Grade2
StudentB Module1 Grade1
StudentB Module2 Grade2

What I tried was to combine each row into a string that looks like this (one cell):

StudentA:Module1:Grade1
StudentA:Module2:Grade2

The newline is generated by using char(10) concatenated. Then on another sheet I refer to the whole table of values (with multiple students) and split it using =arrayformula(split(data,":")). All it did was to make it look like the original table but with names repeated in each row:

StudentA  Module1  Grade1  StudentA Module2  Grade2
StudentB  Module1  Grade1  StudentB Module2  Grade2

Any ideas, anyone?

Rubén Rubén · Accepted Answer · 2020-07-31T22:15:58

In the case preseted in the question, the required result could be achieved by using the Google Sheets way to handle arrays

Example

={A2:C3;A2:A3,D2:E3}

You could combine the above with other functions like SORT.

NOTE:

If your spreadsheet use comma as decimal separator, in the above formula instead of comma use \.

Reference

Using arrays in Google Sheets

Splitting rows into ranges

1 Answers