Home Ask Login Register

Developers Planet

Your answer is one click away!

Alvin February 2016

Compare Values in Excel in all sheets that have the same title/name

Hello fellow netizens of the programming community!

Need some help with a couple of excel worksheets that I'll be working forth day in and out.

Here's a some info of what I have on hand, and what I want to do..

I have several worksheets (10 of them) of similar (not 100% identical) tables ..

one worksheet may look like this (x, y and z are other unimportant variables)

name - score - x - y - z     
jon  -   50  - x - y - z     
sam  -   50  - x - y - z     
pad  -   50  - x - y - z     
fed  -   50  - x - y - z     
mum  -   50  - x - y - z 

and another worksheet may look like this

name - score - x - y - z 
pad  -   50  - x - y - z     
mum  -   50  - x - y - z     
fed  -   50  - x - y - z     
jon  -   50  - x - y - z     
sam  -   50  - x - y - z 

simply put, there are names such as 'jon' and the relevant scores that can occur across all the worksheets, or names such as 'ped' that can only appear once in one worksheet.

I would like to compare all the sheets at the same time, finding out the highest score for jon, sam, pad, fed, mum across all the sheets and have these information presented in another new sheet itself.

for e.g.

the new sheet should present data as

name - highest score
jon - 39    
sam - 22    
pad - 42    
mum - 22

I hope whatever I'm trying to say is not confusing anyone! If anyone could help, ill greatly appreciate it!


M.L February 2016

The solution is available via pivot table formed across multiple sheets. Steps:

  1. Assuming your files is open, and you are in one of your sheets.
  2. Alt+D+P (Opens a dialog box)
  3. Select "Multiple consolidation ranges" and "Pivottable" >>Next
  4. Select "Create a single page field for me" >>Next
  5. Here, select in the Range box and Add, all the table areas one by one. Have one addition per sheet of yours. So you should have 10 ranges getting inserted here. >>Finish

You will get a pivot table where the Values field will have "Count of score" (it is mostly this, else it could be Sum of score, etc. Here's how you change it to max) Click on whatever is in the values field >> Value Field Settings >> Max >> Ok.

(If you want to choose min/average/count/product/stdDev, this is the place to make that change)

As a caveat, do check fields in Filters/Columns/Rows are as you want. I have run a sample pivot on the two tables you have provided, screenshot is attached, this is how the table should look like.

Sample Pivot screenshot on data provided

Post Status

Asked in February 2016
Viewed 1,623 times
Voted 9
Answered 1 times


Leave an answer

Quote of the day: live life