Numerical Sorting

[Richard Green]

Hello,
I'm new to spreadsheets, so please forgive me for asking you to solve possibly an easy problem. (not for me)
All I want to do is to add several groups of numbers to create a group total and then sort with
the highest at the top and show the original row along side the total.
Have a look at my uploaded xls to best explain what I'm talking about.
If I change any of the data under members score cards the column under Total will change
and also the sorted column will change. What I can't do is make the entry column follow the
sorted column. (I have sorted the entry column manually to show how I want it to look).
Can anyone please advise or fix it for me.
Regards
RichardG

[TMS]

The basic formula is:

=INDEX($U$2:U19,MATCH(X2,$V$2:$V$19,0))

As you'll see, it's not quite perfect if you have duplicates. I'll have to look for that.

Regards

[Richard Green]

Well! that was quick, thanks.
I'll give it a go.

Regards
RichardG

[vlady]

hello
Welcome to the forum.

Just give you for a start. Manual Sorting.

when sorting click the column to be sorted and make sure that the expand selection radio button is "selected".

if you want to include the "Members Score Cards" delete column "T"
how?
click the column header "the letter T" then right click choose delete

and another one do not use "=lage" better copy first the "total" then paste it in the "Sorted column" then apply sorting.

This will give you some hints on using sort.

But ofcourse the formula given by TMshucks is much more efficient than using manual sorting (much more advanced).

Regards.

[Richard Green]

Hello VLady,
Thanks for the tips, I'm ok with manually sorting.
TMShucks, suggestion almost works and as he stated has problems with
duplicates, this is very close to what I want, if we can get over that problem I will
be happy

Regards
RichardG

[Reykjavik]

Try this,

Column A to S and U, V in sheet 1
Column W to X in sheet 2

When you change values in sheet 1, your sheet 2 wiill change too.
In sheet 2 you use auto filter column W to X and sort the column you want.

Excel is based on rows (same line). If you sort Column A, Cell A2 will be A4. Then B2 will be B4 also.
If you put your result in another sheet (or below/above table in same sheet) you will cut this connection.

When I am building excel file, I use quite often sheet 1 as data sheet and sheet 2 for result.

Good luck.

[Richard Green]

Thanks Reykjavik,
The closest I have come to what I want is by using TMShucks suggestion.
but doesn't work with duplicates.
So still stuck, any more ideas, Please!

Regards
RichardG

[JBeaucaire]

Here's how I would do it. Ranking works if you add a methodology to introduce a tie-breaking number into the rank calculation. In this method, I've added the "row number" as a decimal, so any ties will still have different actual Sum Codes. Then we rank normally.

Then the same INDEX/MATCH approach works to create the RANK sheet which puts the entire "cards" table into the ranked order.

[Richard Green]

Hi Jerry,
Thanks for taking the time.
I like your method. And this will probably do what I require.
Would it be possible to rank those with equal scores to show as equal.

Regards
Richard

[JBeaucaire]

Ranking yes. But not for the "recreate the whole table in ranked order". For that, the ranks need to be unique so you know what order the table is to be assembled.

[Richard Green]

Thanks Jerry,
I think you've given me sufficient info to experiment.
Certainly have learnt a lot about excel since fooling about with this
little project.

Regards
Richard

[martindwilson]

unique rank the sum to use in the index match,then rank again as normal ranking ,then use the unique rank to pull back the ordinary rank

[JBeaucaire]

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

[daddylonglegs]

=INDEX($U$2:U19,MATCH(X2,$V$2:$V$19,0))

As you'll see, it's not quite perfect if you have duplicates.

To cope with duplicates try this formula in W2

=INDEX(U$2:U$19,SMALL(IF(V$2:V$19=X2,ROW(V$2:V$19)-ROW(V$2)+1),COUNTIF(X$2:X2,X2)))

confirmed with CTRL+SHIFT+ENTER and copied down

[TMS]

@DDL (not DO): Thank you ... I knew there was a way to do it, I just couldn't work it out and I couldn't find a working example (though I'm sure I've got one somewhere!)

Thanks again, TMS

For anyone who's interested, the attached workbook shows all the methods suggested.

[daddylonglegs]

@DO....

Never heard of him........

[TMS]

@DDL: what was I thinking? Apologies, TMS

And just realised I have the wrong person credited in the example

[Marcol]

Late as usual!!

Try this workbook, it uses a helper column (hidden with a grouping button) to produce "Tie-breakers".
The method can be extended by adding more decimal levels e.g. =A1+(B1/100)+(C1/10000) etc.

[Alf]

Or perhaps copy range U2:V19 and paste it as values to range W2.

Then sorted range W2:X19 selecting Key1:=Range("X2"), Order1:=xlDescending, Key2:=Range("W2"), Order2:=xlAscending

Alf