Search code examples
google-sheetsgoogle-sheets-formulaunpivot

Splitting rows into ranges

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?

Solution

  • 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