CODEWITHSUNDEEP
vbaexcel
Wombat
Wombat

Reputation: 9

Creating a list from ranked

I need help writing a loop to find the first ranked team in each conference and add them to a different worksheet.

For instance

Team - Conference - Rank
A - WEST - 1 
B - WEST - 2
C - EAST - 3
D - EAST -4
E - NORTH - 5

Conference winners A, C, and E should be selected even though Team B and D's Ranks are higher. I need to design a loop to filter through 250 teams and 10 Conferences.

Upvotes: 0

Views: 41

Answers (1)

A.S.H
A.S.H

Reputation: 29332

You can do this manually, copying the data, sorting by (conference, rank) then remove duplicates on conference. If there's a reason to do it with VBA, this is the equivalent:

Sub GetFirstByConf()
    With Worksheets.Add
        .name = "FirstTeamByConf"
        Worksheets("Sheet1").UsedRange.Copy .Range("A1")
        .UsedRange.Sort key1:=.Columns("B"), key2:=.Columns("C"), header:=xlYes
        .UsedRange.RemoveDuplicates 2
    End With
End Sub

Upvotes: 1

Related Questions