2

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

Sample list of books

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):

Stacked list

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.

1 Answer 1

0

Formula

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

Explanation

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.