Copying to another sheet and missing out lines

[rireland]

My main sheet has the results of a competition in rows 1-100. 75% of the results won't count for the jumpoff ( its a horsey competition) and I want to automatically copy the successful details to another sheet. I've worked out how to to omit the unsuccessful results by using an IF statement ( IF cellx is > x then copy ) but I still end up with 100 rows - with 25 results and 75 blank rows. How can I copy but compact it down to 25 rows.

As a second question I need to sort the results. I know how to do a manual sort & I even worked out a macro so one keystroke can do the sort. It woruld be better if the sort would be automatic whenever there is a change in the scores.

 

[rireland]

HI Sal - we're getting there. One problem & one improvement needed:
1. If there's equality in the ranking it doesn't work ( e.g. if there's 2 x 3rds then a 5th)
2. If the person isn't ranked ( because they've been eliminated E or retired R then I would like to show that as a score - in no particular order but below all the ranked ones.
3. Also I found a weird problem when I tried it on a bigger sheet - I need a rank of zero for it to work - when I put in 1 to 10 it cut out number 10 but when I put in 0 to 9 it worked fine - I probably made a simple error in a formula somwhere?

scoretest2.xls
	A	B	C	D	E	F	G	H
1
2	INPUT				OUTPUT
3	Name	Score	Rank		Name	Score	Rank
4	Andy	E			Freedy	7	1
5	Bernie	E			Danny	6	2
6	Chris	4	4		Ernie	5	3
7	Danny	6	2		Chris	4	4
8	Ernie	5	3		Andy	E
9	Freedy	7	1		Bernie	E
10	Greg	R			Greg	R
11
12
Sheet1

 

[rireland]

Thanks JB scotland - you've alerted me to a function I didn't know about - thanks. Not quite Ok for this problem as I want something that updates automatically rather than requiring manual operation - the output ( results sheet ) will be projected onto a screen and shows rankings as the results are entered.
Richard

 

[JB_Scotland]

Hi Richard,

Sticking with the Auto Filter option you could have your scores on Sheet1 and your filtered results on a second sheet (Sheet2).

On Sheet2 set your cells to = if(Sheet1!A1="","",Sheet1!A1) copy down and across your data range and format as you wish.

Add the macro below to Sheet1

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    With Worksheets("Sheet2").Range("A1")
        .AutoFilter Field:=6, Criteria1:="4", Operator:=xlTop10Items
        .Sort Key1:=.Range("F1"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
    End With
End Sub

This will filter your results on Sheet2 (Worksheets("Sheet2")) by column F (Field:=6 & .Range("F1")) to show the top 4 plus any ties (Criteria1:="4") in ascending order (Order1:=xlAscending). You can output Sheet2 to your display screen for viewing. Any changes you make to sheet one will automatically update the Sheet2 filtered list.

If you would rather make your entries on Sheet1 and subsequently update Sheet2 you could rename your macro to something like Private Sub UpdateResults() and assign the macro to an 'Update' button on your Sheet1.

Regards JB

 

[rireland]

Autofilter option sounds more promising - especially if it can handle equal rankings. I've probably confused everyone by having just 4 in the example - I actually want to show all - which could be anything up to 30 in a class. But... below the ranked entries should be all those who were not ranked ( who have an E or R in their score column & nothing in the ranking column. Can you amend your formula to accomodate this - sorry to be a pain!

So, to summarise, I start off with a sheet which has all the scores in numerical order of the rider number - the scores & ranking come in from various other sheets - it's in rider numerical order so it's easy to enter scores. Then there's another sheet whose sole purpose is to display the same info but in order of ranking - so the public can see who is winning. If the rider is E or R or doesn't have a score yet (and therefore no ranking) they would just appear below the ranked results. ( in random order - or could be in rider number or alphabetical ) So at the beginning of the day, when there were only a couple of results in, they would appear as 1st & 2nd but as the day progresses they would move down. Hope this makes sense.

Richard

 

[JB_Scotland]

Hi Richard,

It all makes sense.

Set up your scores sheet exactly how you like. e.g. No/Horse/Rider/Points/Jump/Dressage/Cross-Country etc. Name or rename the sheet "Scores" by right clicking the sheet tab.

Create a results sheet named "Results" and use formulas on the sheet to refer to the apropriate cells on the Scores sheet and copy down. e.g. if you have Riders in the third column of the Scores sheet but want Riders displayed in the first column of the Results sheet you would enter in Results cell A1 "= IF(Scores!C1="","",Scores!C1) and copy down a suitable number of rows for the maximum number of anticipated competitors. The IF statement is there to prevent zeros being put in place of blank cells.

The previously posted code applied an Auto Filter to get the top four placed and Sorted by final place. You could do this again, adapting the code, or you could just Sort by the final scores column instead. This way you would see all the riders by place order with the others yet to compete below.

If you were sorting by column "F" (e.g. Total Points) the code you would need for your Scores Worksheet macro would be:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        Worksheets("Results").Range("A1:P200").Sort Key1:=Worksheets("Results").Range("F1"), Order1:=xlAscending, Key2:=Range("A1"), Order2:=xlAscending, Header:= xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
End Sub

The "A1:P200" refers to the Results table area and the "F1" is refering to column "F" for sorting as the first criteria (Total Points). The "A1" is refering to the second sort criteria (e.g. column "A") which might contain rider number.

Let me know if that makes sense.

JB.

 

[rireland]

Thanks - will try it shortly & let you know how it goes.

 

[rireland]

Hi JB

Need a little help - I'm not familiar with macros or VB - apart from the simple ones that you record and play back using an alt key.

I have created the two sheets and sorted the columns and pasted in all the formula - works fine. How do I import your code for the Scores Worksheet macro. I've played around and somehow got the code into a vb file but don't know what to do to make it "go" .


Richard

 

[rireland]

JB - it works ! I had two cells that I'd merged to make it pretty & it didn't like that. However to make it update I need to go into the results sheet and click any cell - is that right?
R

 

[JB_Scotland]

Hi Richard,

Glad to hear you are getting there.

Go to your Scores sheet tab at the bottom. Right click and select View Code. Paste the Private Sub code in here. That should run the macro whenever you change a cell on your Scores sheet - updating your Results sheet automatically.

You must have put the code into the area for your Results sheet instead.

JB.

 

[rireland]

Getting there slowly (its 40degC here so everything is slow). If I paste your code into "scores" I get "Run time error 1004, the sort reference is not valid. Make sure that its within the data you want to sort, and the first sort box isn't the same or blank".

However if I paste it into "results" then it works fine apart from the fact that I have to click a cell in "results" to re-order the results.
The sort range is now A3:N50 & I've changed A1 & F1 to A3 & F3 as there's a couple of rows of headings above the scores.

Is there a way to "turn off" the macro. It was very hard when doing some formatting as it kept moving rows around - in the end I deleted the code & then re-input it.
Cheers
Richard