Dynamic and growing list of topics in a cell for analysis

Hi all,

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 workbook.

The goal of this workbook is to eventually be able to sort and filter by the topics to identify which chapter sections touch on a given topic. Again, there are multiple topics per section, and the folks who will be building this will have to be able to select from previously inputted topics so they don't repeat topics accidentally (e.g., ice cream vs. iced cream). This will eventually be built into 100+ sections and 300+ topics, so usability will have to be taken into account (e.g., it would be tricky to have each topic be its own column). I know this sounds more like a solution that requires a relational database such as Access, but since there would be only two related tables, it would be great if there was a way to solve this in excel, again for usability for users.

Thank you!

-Aaron

In B2 on analysis tab try below and then drag down:

=TEXTJOIN(", ",TRUE,IFERROR(IF(FIND($A2,input!$C$2:$C$1000)>0,input!$B$2:$B$1000,""),""))

update ranges in red if necessary

Another option. In A2:

=LET(t,UNIQUE(TRANSPOSE(TRIM(TEXTSPLIT(TEXTJOIN(",",,input!C2:C5),",")))),t)

In B2 copied down as far as you need:

=IF(A2="","",TEXTJOIN(", ",,FILTER(input!$B$2:$B$5,IFERROR(SEARCH(A2,input!$C$2:$C$5),0))))

Ranges can be set as long as they might need to be.

as for the checklist of topics. does the section and chapter need to be taken into account when creating this checklist? or do you just want a Unique list of topics that have been used already.
Using your existing data a Unique List result would be:
ice cream
cake
pies
sorbet
cookies
baked alaska

Is that all you wanted? or did you want the topics to repeat?

if above unique list is what you are looking for then try below anywhere on the INPUT tab:

Formula:

Please Login or Register  to view this content.

If you want something also to work in GS... PLEASE TELL US in the FIRST post..., not the 9th.