Rank then sort

[Jgood72]

Hi Team,

First post - hopefully I'll give you all the info you need and someone is able to assist...

I have 8 sales people. I gather different results and have created a spreadsheet that will rank 8 people based on results in 7 different categories.

So no problems so far - I have created the Rank formula and it displays the correct positions.

What I'd like to do now is sort the results so instead of having..

John 8
Bev 1
Chris 2
David 3
bob 6
gina 5
Kelly 4
ann 7

I'd like ..

Bev 1
Chris 2
David 3
Kelly 4
Gina 5
Bob 6
Ann 7
John 8


Any assistance greatly appreciated.

Thanks

[brynbaker]

You'll need the rank in a separate column; probably you do already. Select the block. Then it depends on your excel version, but the menu will have something like Data at the top, then sort in the dropdown. Sort on the seond column.

[Jgood72]

Thanks for reply -

I've tried that - it doesn't work.

(Yes the names and the calculate rank are in different cells)

[Jgood72]

Here is desensitized file RANKING HELP.xlsm

[newdoverman]

Copy the Period Ranking then Paste Values (without moving the selection)

Select K26:L33

Click on Sort and Filter, Custom Sort, Deselect My Data has headers

Sort on Column L Smallest to Largest.

If you don't want to do that, enter the rank formula in column G starting at G26 to G33 and select the table and sort on column G.

You could also add a filter to the table like I have shown and sort on column F which means that you wouldn't need the RANK in column G

So you have 3 options shown.

[Jgood72]

Thank you!!!

I love the filter on the table . . . now I need to learn how to make this happen!

Would you be able to assist further?

So I'd like to input the data to the top table.
That would 'filter' the weekly rankings and also when I click the macros button update the period ranking.

Is the filter something that updates automatically as I type in the top table or do I need to create a macros for it to run and adjust ranking.

I guess the weekly ranking and the period ranking are then not required as the team will be ranked according to the score (lowest first)

I'd like to learn how to do this, any help appreciated.

Ultimately, its all about just putting in the data and the rankings appear by themselves.

Thanks again!

[Jgood72]

I went one week further and put in the data for week 3...

File attached - the filter and sort on the period table, creates an error. Whomever is on the top line gets their points instead of the points for the week being allocated to the correct person.

[newdoverman]

I have taken a look at your latest workbook and will TRY to answer your questions.

The filter will update automatically in that the rankings will be there. You however will have to do the sorting unless you incorporate that into your macro.

You are correct that the weekly and period rankings are not required as they are driven directly off the weekly and period tables.

Sorting of the Weekly table is not possible because all the values for each are derived from the top table directly. If you want a sort of the Weekly Ranking, you will have to copy the weekly ranking range and paste values back to clear the formulae from the range..

Seeing the direction that you are going, I wonder if you wouldn't be better served by just having a table at the top that has a column for week number and continue with just one table and use the filtering power of tables to get your weekly and period ranking statistics.

The error that you are experiencing with the top line getting the values that it shouldn't I think is an error in how the macro works. If your macro works the way that I think it does, both the source table (top table) and the destination table (bottom table) will have to be sorted into the same order BEFORE the data is taken from the top table and inserted into the bottom table and the copy range has to be exactly the same size as the destination range.

I think that all this would be unnecessary if you just had one table and generated all your reports as required from it. It might be worth experimenting with just one table to see how that works with your data.

[Jgood72]

OK I'll have a look at that and post a spreadsheet in a few days that will reflect my attempt!

Thanks again