Excel compare data and rank in order..

[sunshine_atl]

Hello,
I have an excel spreadsheet that has 4 different tabs of data for each individual (same parameters with different data values), can Excel do a comparison of the data to show, based on the data, a ranking say from the least to the greatest or vice versa? So which manager has the highest averages for cases reviewed, least amount of errors, least amount of late cases, etc. and rank them in order for each parameter. I have the excel sheet attached and am trying to get this functional in the "Consolidated" tab. Appreciate any inputs on how this can be done.

Thanks.

[Glenn Kennedy]

It would be MUCH MUCH easier to have ONE sheet for the raw data and another column for Manager name. Excel (especially the older versions) works best with big, flat, boring blocks of data.

In Excel 2013, multi-sheet formulae are messy, long-winded and slow. Are you REALLY stuck with the multi-sheet structure?

[sunshine_atl]

The data is in such a way that each manager has the data values for the same parameters and is listed on separate worksheets in the same workbook (as shown in the example in attached sheet). So trying to know if there is a way I can have a ranking for each manager for each manager (rate them from high to low for each parameter), so we can know who is perfoming better and make a decision. Not sure if this can be done through some excel function or will require a macro. thanks for your guidance and appreciate inputs if anyone knows how this can be achieved.

[Glenn Kennedy]

Unfortunately, your results sheet is entirely empty, so I have no idea what you really want.

This is a starter...

1. Refer to the file.

2. Set up a Named range (CTRL-F3 to view - edit) which is a list of sheets you wish to check.

3. B2, copied down:
=MAX('Manager 1'!B1,'Manager 2'!B1,'Manager 3'!B1)

4. C2, copied down:
=INDEX(List,MATCH(1,COUNTIF(INDIRECT("'"&List&"'!"&ADDRESS(ROWS(C$2:C2),2)),B2),0))

You seem to be using an older version of Excel than me. So, please refer to the attached file. The formula used at 4) are array formulae.

These are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see the curly brackets { } appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[sunshine_atl]

Thanks much for the response. I am actually looking for an output as shown in the attached sheet (within the Consolidated tab). So basically looking for a ranking of the data for various parameters. Also trying to know what exactly you have in the column M. I am able to access the file you sent, it's just not showing any curly brackets. I did try to use the Ctrl+Shift+Enter in cell 1, but it did not do anything. may be I am missing something.

[Glenn Kennedy]

I will look again... BUT what Excel version are you ACTUALLY using? Is it 2013???

[Glenn Kennedy]

See file for modified formule. I still don't know what your current Excel version is, though. That is ESSENTIAL information.

[sunshine_atl]

Just checked within Account - About Excel - Microsoft® Excel® for Microsoft 365 MSO

[Glenn Kennedy]

So, please modify your profile to show your current version.

[Glenn Kennedy]

Is the manager's name held in any cell on the individual sheet - or just (a pain in the @ss...) just in the Tab name?

[Glenn Kennedy]

No reply. I have to leave shortly... purple cells show spill formulae for the parameters you want. The retrieval of the manager's name could be simpler if it was also hels in a cell on each sheet.

[Glenn Kennedy]

That still doesn't tell me which version NUMBER you have (2210 is the latest release - you may/may not have it yet, depending on your subscription).

However, this might do the job:

=LET(a,VSTACK(M1:M3,'Manager 1:Manager 3'!B1),r,ROWS(a)/2,b,WRAPCOLS(a,r),SORTBY(b,INDEX(b,,2),-1))

It will spill the results across and down. In subsequent cells, change the bit in BLUE to B2, etc. To sort results in ASCENDING order, change the GREEN -1 to 1

Make sure that M1 to M3 are in the SAME order of appearance as the sheets in your file. If there IS a cell in each sheet with the manager's name in it (information requested previously, but not yet supplied), this requirement can be removed with a simple formula tweak.