Excel’s PivotTable feature can handle this request, but you have to invoke it with an uncommon setting called Multiple Consolidation Ranges. This is only the second time I’ve needed that particular setting. First, let’s get all the required data into a single consolidation workbook. Rather than make copies, we’ll create links to the original data. Create a new workbook and insert a worksheet for each of the workbooks you want to consolidate. Click one worksheet tab, then Ctrl-Click the rest to select them all. Enter “Item” in cell A1 and “Source” in cell B1. Because the tabs are all selected, these entries will appear in every worksheet. Click a single tab to end the multiple selection.
Open the first of the source workbooks and highlight just the list of items. Copy it to the clipboard. Switch to the consolidation workbook and click in cell A2 of the corresponding worksheet. Select Edit | Paste Special and click the Paste Link button. Highlight the cells in column B from row 2 to the last row containing data. Enter an identifying name for the source workbook and press Ctrl-Enter to put the name in each of those cells. Repeat for all the other source workbooks. You now have a single workbook that pulls together the item data from all the source workbooks.
Select Data | PivotTable and PivotChart Report from the menu. Check the Multiple Consolidation Ranges option and click Next twice.
Now you will select the two-column data ranges from each of the sheets, one at a time, and add them to the list of ranges. When you’ve added them all, click Finish. You now have a PivotTable in a new worksheet. In the table all the items appear just once, followed by a count of how many times that item appears. That’s useful, but we can make it even better.
From the field list, drag the item Value and drop it on the PivotTable column head named Source. Now right-click the Source column head and choose Hide. The chart now shows a row for each unique item and a column for each source workbook, with the intersections showing how many times each item appears in each workbook. A Grand Total column at far right shows the total number of appearances in all workbooks.
You can also use the Multiple Consolidation Ranges feature to analyze more data points than Excel XP or 2003’s 65,536-row limit allows. Just put the items in as many columns as needed on one sheet, then select each column as one of the ranges. Of course, if you force Excel to analyze hundreds of thousands of items, you may have to wait quite a while for the PivotTable calculation to complete.