Reply To: Combining/Merging indices for a series of journals that belong to a single vol.

I told Mr. Excel (Bill Jelen) about this thread, and he sent me this via email (he was about to teach a class so he couldn’t log in to post it). Bill will be speaking at The InDesign Conference this June.

Step 1:
Get each page number separated to a new row. You can use the InDesign technique suggested by Obi Wan Kenobi. If you have Excel for Windows, the new Power Query Split by Delimiter To Rows (https://www.youtube.com/watch?v=nGygtOutMtg) will solve it. In Mac Excel, you will have to use Data, Text to Columns, and then some copying and pasting.

Step 2. You have topics in column A, single page number in B. Headings in Row 1 and data starts in row 2.

Sort the data by Page number within topic.

Heading in C1: Temp Result
Formula in C2:
=IF(A2=A1,C1&”, “&B2,”B2)
Copy that formula down
Heading in D1. Best Answer For Topic
Formula in D2: =IF(B2<>B3,True,False)
Copy that formula down

Select Column C. Copy. Paste as Values.
Turn on the Filters using Data, Filter.
Filter column D to True
While filtered, copy column C and paste back to InDesign.