Cross reference two overlapping data sets in the same table (matrix?)

I have two sets of data that I want to cross-reference. The sets of data are the number of chapters and the names of characters that appear in those chapters. I want to be able to view data point "Chapter 1" and see what characters appear there (such as Kyra, Betty, John, Garic). Then I want to be able to view data point "Kyra" and see what chapters she appears in (Chapter 1, Chapter 2, Chapter 4, etc.).

Currently, I have the data arranged in two separate lists (which is obviously very ineffective). Is there a way to combine these data points into a single format so that they are all together and navigable? There are hundreds of each data point, and creating a matrix (the kind with every data point on both axes and an X where they overlap) would be obviously very inefficient.

Current format:
excel.png

I cannot see png files. Can you upload an example spreadsheet (Go Advanced>Manage Attachments)

Okay, assuming it's okay for the chapters to be in individual cells, you can do this with an ARRAYED formula as follows

In C10, I put this formula
=IFERROR(INDEX($A$3:$A$7, SMALL(IF(ISNUMBER(SEARCH($A10,$B$3:$B$7)), ROW($A$3:$A$7)-2),COLUMNS($A$1:A$1))),"")
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Then copy across and down
See attachment.

Yes, the array formula could work across sheets. With so many characters and chapters, you would need to use VBA to get all the chapters into a single cell.

Reattaching with table 2 on different sheet. I am also including another option with checkmarks in chapters where characters appear.

If I use the following macro, how can I select an entire range as a row, but tell it to end the text string at the blank cells (aka: don't display the ", " for all the blank remaining cells)? Would that be a conditional change to the macro, or something to do in the cell function with an if/else?

[MACRO]

Sub ConcatenateRange()
'
' ConcatenateRange Macro
' Combine multiple cells into a single text string.
'
' Keyboard Shortcut: Ctrl+o
'
Function ConcatinateRange(sourceRange As Excel.Range) As String
Dim finalValue As String

Dim cell As Excel.Range

For Each cell In sourceRange.Cells
finalValue = finalValue + CStr(cell.Value) + ", "
Next cell

ConcatinateRange = finalValue
End Function

I added an IF ELSE section

Please Login or Register  to view this content.

I added a table created by the macro. in Col B, I did it the same way you did (with the new macro). In B, I didn't assume that the names would be in the same order so it includes a MATCH function to make sure that the right character is chosen.

This macro doesn't require any of the intermediate tables. Characters is a named range in the workbook (see attached). It reads directly from your first table (Character-Chapters)

I used this in cells 'By Character'!D11:D15

Please Login or Register  to view this content.

Okay, in this version, I made use of that last macro (post #10) to completely remove the Arrayed functions (they will just slow down your system). Look at Formulas Tab>Name Manager to see the dynamic named range I created there.

I now modified the macro so that it looks for either the name at the end of the string

Please Login or Register  to view this content.

or if not there, then followed by a comma

Please Login or Register  to view this content.

That should do the trick and worked in the example with Johnathan.
New Macro

Please Login or Register  to view this content.