I don't know if posting external links are allowed. But I found this VBA script for excel very helpful in creating a map grid.
https://www.extendoffice.com/documents/excel/2538-excel-transpose-duplicate-row-to-columns.html
I too did the spatial join to get street names and page numbers. A few fields to make this work are 1 field with just the street name, 1 field with just the Page Number, and then 1 field that puts them together. I used the Field that has them put together to run the summarize tool, to remove any of the duplicates. Then I keep the Street Name and Page number fields for the excel tool. What it does is take the street name looks for duplicates and then takes the page number and puts them into new Columns in stead of Rows. Then you can use the TEXTJOIN() function in excel to combined all the page numbers together. For Example:
Starting with This
Street Name | Page Number |
N MAIN ST | A1 |
N MAIN ST | A2 |
N MAIN ST | A3 |
EASY ST | C5 |
ROCK RD | H3 |
ROCK RD | H4 |
You would get this (after running the vba code)
Street Name | Page Number | Page Number | Page Number |
N MAIN ST | A1 | A2 | A3 |
EASY ST | C5 | | |
ROCK RD | H3 | H4 | |
Then I create a new Column and use the TEXTJOIN() function to get
Street Name | Page Number |
N MAIN ST | A1, A2, A3 |
EASY ST | C5 |
ROCK RD | H3, H4 |
I then insert that excel table as an object to my layout, or you could export to an image and insert that way as well.
Hope this helps someone