automatic data sort

[rireland]

Is there a way I can do a automatic sort, every minute for example. My final results of a sporting competition are on sheet 9. This sheet is displayed on a separate screen (for the crowds to see). The results are ranked and this changes as new results come in.
At present I need to go to sheet 9, highlight relevant area, and do a sort by column x.
I'd like this to happen automatically, we would enter data in sheets 1-8 and sheet 9 would show the results in ranked order. Updated at regular intervals.

Richard

 

[acw]

Hi

I'm guessing that sheet9 brings in /consolidates the data from sheets 1-8. If you put in a ranking on that data, then in your output area have the rankings listed (1,2,3...). You could then use either vlookup, or index/match to bring in the relevant data from the consolidated section to match the fixed ranking. As the data was updated, the output would automatically update.


HTH

Tony

 

[rireland]

Thanks, great idea but.... I'm familiar with vlookup but not index/match. Don't know if it could handle something I forgot to mention, which is equalities. Instead of the ranking being 1,2,3,4,5,6 it could be 1,2,2,4,5,6. So I can't have a fixed ranking to match up to.
Richard

 

[PGuru]

I've got a live sort formula somewhere but I can't find it at present. From memory it also deals with equal second place. I'll try and post it in the next couple of days.

 

[acw]

Hi

There is a unique rank process around that will overcome (to some extent) the standard rank.

Say your data being ranked is in A1:A8. Put the following formula in B1

=RANK(A1,$A$1:$A$8,1)+COUNTIF($A$1:A1,A1)-1

and copy down to B8. This will give a unique ranking to each item. It does rank each of the "common" items based on their position in the order.

Tony

 

[PGuru]

That's it - then put this in C1

=OFFSET(A$1,MATCH(SMALL(B$1:B$8,ROW()-ROW(C$1)+1),(B$1:B$8),0)-1,0)

And you should get names in ranking sequence