Counting values across multiple sheets

Question

I thought this would be a simple thing to do - but apparently not?

I have a workbook with several sheets. Each sheet contains a column of names; some of these sheets have 40,000 - 50,000 rows each with a different name. Each name is unique within each sheet.

What I want to do is create a report that tells me how many times each name appears in the entire workbook, ie how many sheets each name appears on. I thought this was basic pivot table functionality but when I try and do this I get the error "This command requires at least two rows of source data. You cannot use the command on a selection in only one row ..... If you're creating a Pivot Table... type a cell reference or select a range that includes at least two rows of data"

The data I selected has, of course, over 200,000 rows of data so definitely more than one. How do I do this? Thanks.

Answer 1

Hi,

I suggest you append data from multiple worksheets first.  Then you can build a Pivot Table.  See this video - https://www.youtube.com/watch?v=yL11ugShdrk.

Hope this helps.

Answer 2

You need to select at least 2 columns to make it happen. In your resultant consolidated pivot table, you can remove column B.

hence, you need to select Sheet1!$A:$B and so on for other sheets.

Answer 3

Thanks for quick response. I was using the wizard with "Multiple consolidation ranges" selected. I then added the column of names from each sheet, so:

Sheet1!$A:$A

Sheet2!$A:$A

Sheet3!$A:$A

etc

Cheers!

Answer 4

While making pivot table, can you let me know what is appearing in the red zone