Creating a list of unique values by checking data across multiple worksheets

Question

I'm trying to create a formula to search a list of titles across multiple worksheets (all in the same workbook), and return a list of unique titles on a summary sheet. This is an image of by workbook:

There is one summary sheet, plus a worksheet for each month, each with a list of book titles on it. Many of the book titles will be duplicated on each months worksheet, but there will be some new titles that don't appear on the previous months worksheet. I want the summary sheet to search each of the individual months spreadsheet simultaneously, and insert any new titles that haven't appeared before. I've got this to work when only having to search one other worksheet, but can't get the search happening across multiple spreadsheets. Each of the monthly spreadsheets are identical (in format) to the summary spreadsheet shown, and each contains between 800 and 900 titles (although this may increase in future months).

This is what I've come up with so far:

{=INDEX(INDIRECT("'"&Sheetnames&"'!"&ADDRESS(ROW(),2,3)),(MATCH(TRUE,COUNTIF(B$6:B6,INDIRECT("'"&Sheetnames&"'!"&ADDRESS(ROW(),2,3)))=0,0)))}

where 'Sheetnames' refers to a range on a separate worksheet that lists the individual sheet names (i.e. Press (Jan), Press (Feb), etc.).

The B$6:B6 range in the COUNTIF section will change as the formula is copied down the column, so that cell B10 for example, will have B$6:B9, so that it is (or should be)checking titles that are already in the summary list against the 12 monthly worksheets and ignoring any that are already there.

This still only seems to be checking against the first worksheet though (Press (Jan)), and not the other 11 sheets.

Anybody have any ideas? An explanation of how any changes to the formula above works would be appreciated.

Answer 1

Worksheet formulas are the wrong approach.

The best approach is to convert your workbook to a database, using one sheet, adding a column of date or month values that allows you to filter/summarize/sort/pivot your data as desired.

The next best approaches are:

• Convert your monthly data sets to tables, and use a data query to extract and summarize the data.
• Use a pivot table to consolidate the data ranges and summarize the data

Answer 2

Hi,

This is quite a simple problem to solve using Power Query.  If you can download that free add-in from the Microsoft website, then we can get started.  Also, the Summary sheet will get created in another file.  Furthermore, the Power Query technique will be dynamic enough to get your desired result even if more files are added to the folder where we place the source input files or more sheets are added to the same file.