Google-sheets – way to group rows by a term and insert this term as a header in Google Sheets

google sheets

I have a list of books on Google Sheets that will be turned into a paged layout on InDesign. Each book has two additional information besides its title (year and theme).
Like this

But I need this list to be structured in a stacked way. Grouping first by year, and then by theme, like this (I've formatted the cells just to clarify):

I currently do this stacking manually, and it requires quite a lot of time. Is there some sort of formula, or tool, to solve this problem? I guess it can be done with scripting, but I wanted to know if there's a non-scripting way to do it before diving into Google sheets script documentation.

Best Answer

Formula

=ArrayFormula(TRANSPOSE(SPLIT(JOIN(",",IF(A2:A10=A1:A9,,"Year "&A2:A10)&","&C2:C10&","&IF(B2:B10=B1:B9,,B2:B10)),",")))

Explanation

We could use a 2D references to make comparisons, i.e. A2:A10=A1:A9.
Using and empty argument for the value if true or value if false of IF returns a blank.
& concatenates values and functions results. We are using a comma as separator between IF results and the values of C2:C10.
JOIN joins the values of a 1 x n or n x 1 range. We are using a comma as separator.
SPLITS separate subtext of a text. We are using a comma as subtext delimiter.
ArrayFormula expands the result of the inner formula to range of the required size by the result.

Partial answer outline

Simplified case

Source data: 1 column, | (pipe character) as separator.
Cell B1 formula:

=Transpose(Split(Join("|",A1:A2),"|"))

Formula Explanation

First, join the cells values to make one list, using a character as separator.
Second, separate the list elements at each separator.
Third, change range from m rows and n columns to n rows and m columns.

Example

Adaptation

To make the above work for the current version of the question, consider the following steps:

Assume that the first column values will be previously filled up, and it's not required to maintain the relation with the second and third columns. These values only serve as custom row IDs.
Add a second sheet.
Copy the first column.
Use the formula of the simplified case as a base for an ad-hoc formula for columns two and three. Edit the formulas to include SUBSTITUTE to add a | before each <br>.

Google-sheets – Insert custom number of new rows in Google Sheet

Select multiple rows, right click on any selected row, and you will now see options to "Insert X above/below" where X is the number of rows you have selected. As an example:

In order to insert 10 new rows:

Select 10 rows

Right click on any of the selected rows

Select "Insert 10 above" or "Insert 10 below"

You can also select multiple rows, and then open the Insert menu. The options will also be available there. As an example:

In order to insert 10 new rows:

Select 10 rows

Click on the Insert menu on the top menu bar

Select "10 Rows above" or "10 Rows below"