# Team results problem

#### sh1pley

##### Board Regular
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?!

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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?

The data is very straight forward. It comes in an excel sheet with:

Poisition, Name of runner, Team, Time

Ship,

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

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

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``````

Legend!

Replies
0
Views
263
Replies
3
Views
922
Replies
2
Views
177
Replies
2
Views
234
Replies
1
Views
317

1,211,679
Messages
6,103,244
Members
447,849
Latest member
4chase

### 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.

### Which adblocker are you using?

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

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