Team results problem

sh1pley

Board Regular
Joined
Dec 22, 2006
Messages
160
I am trying to compile some cross country team results. The race is split into two divisions. A team consists of 12 runners and Team Shipley has a team in division 1 and a team in division 2.

What I need to be able to do is cut the first 12 Team Shipley runners from the results sheet and paste into a new sheet to become the division 1 results leaving the rest as division 2 results.

Also if for example Team Shipley only had 7 runners then I would need to cut only those 7 from the results sheet so its not always 12 each time.

What I thought I would have to do is sort the results by Team name first and then have some VBA code to cut the runners I need from the results?

I hope this is clear?!
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi Shipley

What format do the results come thru as? Is it a 'standard' data table ie fileds across the columns records in individual rows? If so, some form of Autofilter code would seem most appropriate.

Perhaps you could post a small sample of the data using one of the html makers?
 

sh1pley

Board Regular
Joined
Dec 22, 2006
Messages
160
The data is very straight forward. It comes in an excel sheet with:

Poisition, Name of runner, Team, Time
 

Paddy1979

Well-known Member
Joined
Sep 23, 2005
Messages
608
Ship,

How can you differentiate between Team Shipley division 1 and Team Shipley division 2 in the table?
 

sh1pley

Board Regular
Joined
Dec 22, 2006
Messages
160

ADVERTISEMENT

This is the tricky bit. Anyone in team Shipley can be in division 1. The first 12 runners to finish from Team Shipley become division 1, all the rest will then become division 2

So I need a way to go down the results and cut these 12 runners from the results to leave the division 2 runners
 

Paddy1979

Well-known Member
Joined
Sep 23, 2005
Messages
608
I think this will do the trick it works from the bottom upwards and ignores the first 12 Team Shipleys it finds then cuts and pastes any others it finds onto sheet2

Code:
Sub cutship()

Dim c As Long
Dim n As Integer
Dim i As Integer

finalrow = Range("c" & Rows.Count).End(xlUp).Row
n = 1
i = 0

With Range("A1:D" & finalrow)
    .Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With

For c = finalrow To 1 Step -1
 If Cells(c, "C").Value = "Team Shipley" Then
   i = i + 1
   If i < 13 Then GoTo here
   Rows(c).Cut Sheets("Sheet2").Range("A" & n)
   n = n + 1
 End If
here:
Next c


End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,656
Messages
5,597,390
Members
414,141
Latest member
Joey_T92

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top