Ordering two cells by the value of another

Excel Excel 2007 home and student
February 17, 2010 at 16:19:51
Specs: Windows XP
Hi. I'm building a small worksheet for some productvity numbers. I have a basic spreadsheet set up that calculates the productivity of a worker by dividing their sales by hours worked. I'd like to make it so it automatically reorders another set of cells with the workers names from top productivity to bottom, peferrably with their personal productivity attached.

RIght now the workers names are in cells A3 to A13, the final productivity is in B3 to B13, hours worked are C3 to C13, and sales are in D3 to D13. The productivity formula is simple just =QUOTIENT(D3,C3) for example. Is what I want possible? Thanks!

See More: Ordering two cells by the value of another

February 17, 2010 at 22:10:49
What do you want to happen when there is a tie?

Report •

February 17, 2010 at 22:34:44
Probably just do it alphabetically. although most of them are something along the lines of 2254/38.74, how many decimals can it go to?

Report •

February 18, 2010 at 09:26:40
I think that to manage ties with an alphabetical list of the names is going to require a macro.

However, if you are willing to settle for a non-alphabetical list if there is a tie, then this formula based solution should work:

To start with, I would limit the chances for a tie by replacing the QUOTIENT function with the actual division e.g =D3/C3. You can still display the results in integer format by not showing the decimals, but there would be much less chance of a tie.

You can further ensure against a tie by adding "an offset" to the end of each formula:


This will change the value of the number in the cell just enough to eliminate any ties, but probably not impact the actual results.

Once that's done, You can use LARGE to list the Productivity numbers in descending order:


To avoid having to manually type in the 1, 2, 3, etc. you can use:

=LARGE($B$3:$B$13,ROW()) and drag the formula down.

If you start in Row 1, ROW will increment each time and return 1, 2, 3, etc. If you start in another, e.g. Row 3, use:


ROW()-2 will evaluate to 1, 2, 3 as you drag it down.

OK, now that you have your Productivity numbers in descending order, we need the names. Normally, VLOOKUP is used to look up a value in a table and return a value from a specific column within that table. Unfortuately, VLOOKUP only looks to the right and we need to look left to pick up the name.

Try this:

Let's say you put your LARGE function starting in F1. In E1, you could use this:


The MATCH portion will match the value in F1 with the same value in B1:B13 and return it's postion, e.g. 5.

The INDEX function will then use that value as the row_num and return the value in e.g. A5, which should be the name of the person associated with that Productivity number.

Give that a try.

Edited 2/21/10 to fix function errors

Report •

Related Solutions

February 18, 2010 at 10:23:39
Wow that's awesome, thanks! I'm off work for the next two days but as soon as I go back I'll try it!

Report •

February 21, 2010 at 12:49:27
Just set it all up, it wasn't working at first but I took the quotations off of =LARGE("B3:B13",1) etc, and it worked perfectl! Thanks again!

Report •

February 21, 2010 at 14:36:31
I have edited my Response to fix the quote errors.

I must have been tired - I seem to have mixed VBA syntax with Excel Function syntax. Sorry!

In any case, I fixed the functions in that post so the correct syntax will be stored in the archives and not confuse anyone in the future.

Report •

Ask Question