My issue is that I am building a workbook (see minimum viable example attached) that identifies multiple topics that occur in each section of a book chapter for multiple book chapters. This workbook will require the users to be able to see a growing list of multiple topics (i.e., categorical data) as an index somehow as they're inputting the data.

Each row is a distinct section of a chapter (e.g., 1.1, 1.2, 2.1, 2.2...), and the primary column (or columns) is a list of topics. There will also be columns with information on each chapter section (e.g., title). See my sample below:

enter image description here

And I want to be able to automatically generate a continuously added list like this:

enter image description here

For excel, I was provided with an example formula solution that doesn't have sheets compatibility:


Thank you!



1 Answer 1


Try this:


Let me know ;)

  • Hello @doubleunary, yes, by default they're TRUE, that's why I didn't specify them
    – Martín
    Nov 10 at 16:55
  • Thanks for this solution, there is a small issue with it in that it logs before and after commas different on the list. For example, "cookies, bread" would log "cookies" as an item, and then "bread, cookies" would log " cookies" as a separate item. Looks like =ArrayFormula(UNIQUE(TRANSPOSE(TRIM(SPLIT(TEXTJOIN(",",1,Sheet1!C2:C5),","))))) as suggested solves that problem.
    – agrade
    Nov 10 at 17:16

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.