<p>I have an excel spreadsheet in a format similar to the following...</p>

<pre><code>| NAME  | CLUB | STATUS | SCORE |
| Fred  |  a   | Gent   | 145   |
| Bert  |  a   | Gent   | 150   |
| Harry |  a   | Gent   | 195   |
| Jim   |  a   | Gent   | 150   |
| Clare |  a   | Lady   | 99    |
| Simon |  a   | Junior | 130   |
| John  |  b   | Junior | 130   |
   :
   :
| Henry |  z   | Gent   | 200   |
</code></pre>

<p>I need to convert this table into a list of the "Top Ten" teams. The rules are</p>

<ul>
<li>Each team score is taken from the sum of four members of that club.</li>
<li>These totals should be of the best four scores except...

<ul>
<li>Each team must consist of at least one Junior or Lady</li>
</ul></li>
</ul>

<p>For example in the table above the team score for club A would be 625 <strong>not</strong> 640 as you would take the scores for Harry(190), Bert(150), Jim(150), and Simon(130). You could not take Fred's(145) score as that would give you only Gents.</p>

<p>My question is, can this be done easily as a series of Excel formula, or will I need to resort to using something more procedural?</p>

<p>Ideally the solution needs to be automatic in the team selections, I don't want to have to create separate hand crafted formula for each team. I also will not necessarily have a neatly ordered list of each clubs members. Although I could probably generate the list via an extra calculation sheet.</p>

[{'answer_id': 57955, 'author': 'Knox', 'author_id': 4873, 'author_profile': 'https://Stackoverflow.com/users/4873', 'pm_score': 0, 'selected': False, 'text': "<p>Use a pivot table which will act as a database query on the data you have.  Pivot so that the teams go down the columns and team members along with their status type go across the pivot table.  I'm not sure for 2003, but Excel 2007 lets you then sort so the highest scores appear to the left.  Then your first sum can simply take the first three scores for the each team.  However to get the last persons sum, you have to determine if you can use the 4th score, or if you have to use the max of the junior or Lady types.  That could be done using a complex and brute force formula somewhat like this:</p>\n\n<p>if (type of position 1 is a junior or a lady or ... 2 or 3... ) then use position 4 else if position 5 is a junior or lady then use 5 else if p 6 is ... and so on.</p>\n"}, {'answer_id': 57959, 'author': 'GEOCHET', 'author_id': 5640, 'author_profile': 'https://Stackoverflow.com/users/5640', 'pm_score': 0, 'selected': False, 'text': '<p>Writing a solution in VBA would be my first choice, especially if the rules have the possibility of becoming more complex.</p>\n'}, {'answer_id': 57981, 'author': 'Prestaul', 'author_id': 5628, 'author_profile': 'https://Stackoverflow.com/users/5628', 'pm_score': 0, 'selected': False, 'text': "<p>I don't think that this can be done unless the table is sorted in some way.  Most of Excel's lookup functions require ordered lists.  This could certainly be done with a VBA function.</p>\n"}, {'answer_id': 60187, 'author': 'Dick Kusleika', 'author_id': 4280, 'author_profile': 'https://Stackoverflow.com/users/4280', 'pm_score': 3, 'selected': True, 'text': '<pre><code>Public Function TopTen(Club As String, Scores As Range)\n\n    Dim i As Long\n    Dim vaScores As Variant\n    Dim bLady As Boolean\n    Dim lCnt As Long\n    Dim lTotal As Long\n\n    vaScores = FilterOnClub(Scores.Value, Club)\n    vaScores = SortOnScore(vaScores)\n\n    For i = LBound(vaScores, 2) To UBound(vaScores, 2)\n        If lCnt = 3 And Not bLady Then\n            If vaScores(3, i) &lt;&gt; "Gent" Then\n                lTotal = lTotal + vaScores(4, i)\n                bLady = True\n                lCnt = lCnt + 1\n            End If\n        Else\n            lTotal = lTotal + vaScores(4, i)\n            lCnt = lCnt + 1\n            If vaScores(3, i) &lt;&gt; "Gent" Then bLady = True\n        End If\n\n        If lCnt = 4 Then Exit For\n    Next i\n\n    TopTen = lTotal\n\nEnd Function\n\nPrivate Function FilterOnClub(vaScores As Variant, sClub As String) As Variant\n\n    Dim i As Long, j As Long\n    Dim aTemp() As Variant\n\n    For i = LBound(vaScores, 1) To UBound(vaScores, 1)\n        If vaScores(i, 2) = sClub Then\n            j = j + 1\n            ReDim Preserve aTemp(1 To 4, 1 To j)\n            aTemp(1, j) = vaScores(i, 1)\n            aTemp(2, j) = vaScores(i, 2)\n            aTemp(3, j) = vaScores(i, 3)\n            aTemp(4, j) = vaScores(i, 4)\n        End If\n    Next i\n\n    FilterOnClub = aTemp\n\nEnd Function\n\nPrivate Function SortOnScore(vaScores As Variant) As Variant\n\n    Dim i As Long, j As Long, k As Long\n    Dim aTemp(1 To 4) As Variant\n\n    For i = 1 To UBound(vaScores, 2) - 1\n        For j = i To UBound(vaScores, 2)\n            If vaScores(4, i) &lt; vaScores(4, j) Then\n                For k = 1 To 4\n                    aTemp(k) = vaScores(k, j)\n                    vaScores(k, j) = vaScores(k, i)\n                    vaScores(k, i) = aTemp(k)\n                Next k\n            End If\n        Next j\n    Next i\n\n    SortOnScore = vaScores\n\nEnd Function\n</code></pre>\n\n<p>Use as <code>=TopTen(H2,$B$2:$E$30)</code> where <code>H2</code> contains the club letter.</p>\n'}, {'answer_id': 62187, 'author': 'Mike Woodhouse', 'author_id': 1060, 'author_profile': 'https://Stackoverflow.com/users/1060', 'pm_score': 2, 'selected': False, 'text': '<blockquote>\n  <p>can this be done easily as a series of\n  Excel formula</p>\n</blockquote>\n\n<p>Short answer, YES. (Depending on your definition of "easily"). </p>\n\n<p>Long answer...</p>\n\n<p>(I <em>think</em> this works)</p>\n\n<p>Here\'s my (brief) test data:</p>\n\n<pre><code>\n    A          B    C        D\n 1 NAME      CLUB STATUS  SCORE\n 2 Kevin    a   Gent    145\n 3 Lyle     a   Gent    150\n 4 Martin   a   Gent    195\n 5 Norm     a   Gent    150\n 6 Oonagh   a   Lady    100\n 7 Arthur   b   Gent    200\n 8 Brian    b   Gent    210\n 9 Charlie  b   Gent    190\n10 Donald   b   Gent    220\n11 Eddie    b   Junior  150\n12 Quentin  c   Gent    145\n13 Ryan     c   Gent    150\n14 Sheila   c   Lady    195\n15 Trevor   c   Gent    150\n16 Ursula   c   Junior  200\n</code></pre>\n\n<p>Now, if I\'ve understood the rules correctly, we want the best four scores, except that if the highest score by either a lady or a junior is not in the best four, we use that instead of the fourth highest. I\'ve restated it somewhat, for reasons that may become apparent...</p>\n\n<p>OK. Array formulae to the rescue! (I hope)</p>\n\n<p>The highest score from team a should be</p>\n\n<pre><code>{=LARGE(IF(B2:B16="a",D2:D16,0),1)}\n</code></pre>\n\n<p>where the {} indicates an array formula created by using Control-Shift-Enter to input the formula. The top four are similarly created. For the Lady/Junior bit, we need a bit more complexity. Taking the Lady, we need this:</p>\n\n<pre><code>{=LARGE(IF($B$2:$B$16=$J3,IF($C$2:$C$16="Lady",$D$2:$D$16,0),0),1)}\n</code></pre>\n\n<p>Junior may safely be left as an exercise for the student, I hope.</p>\n\n<p>I\'m now looking at a table with the following layout for club "a"</p>\n\n<pre><code>\n     J    K      L      M      N      O      P\n 1 Club    1      2      3      4   Lady  Junior\n 2 a     195    150    150    145    100      0\n</code></pre>\n\n<p>The club score should be the top three "anyone" scores plus the best lady or junior <em>if they\'re not already in the top four</em>.</p>\n\n<p>So in Q2 I\'m putting this:</p>\n\n<pre><code>=SUM(K2:M2)+MIN(MAX(O2,P2),N2)\n</code></pre>\n\n<p>MAX(O2,P2) tells me the best lady or junior score, which has to be included. If it\'s higher than the fourth-highest team score, then it\'s already in the list and we just take the top four. Otherwise, we replace the fourth-highest score with the best lady/junior one.</p>\n\n<p>Now we could do it all in one formula, by substituting the parts into the final formula:</p>\n\n<pre><code>{=LARGE(IF($B$2:$B$16=$J3,$D$2:$D$16,0),1)+\nLARGE(IF($B$2:$B$16=$J3,$D$2:$D$16,0),2)+\nLARGE(IF($B$2:$B$16=$J3,$D$2:$D$16,0),3)+\nMIN(LARGE(IF($B$2:$B$16=$J3,$D$2:$D$16,0),4),\nMAX(LARGE(IF($B$2:$B$18=$J3,IF($C$2:$C$18="Lady",$D$2:$D$18,0),0),1),\nLARGE(IF($B$2:$B$18=$J3,IF($C$2:$C$18="Junior",$D$2:$D$18,0),0),1)))}\n</code></pre>\n\n<p>But I don\'t recommend it...</p>\n\n<p>So for the above data, I end up with this:</p>\n\n<pre><code>\n            Anyone                                          Lady        Junior                \nClub        1           2           3           4           1           1           Total     \na           195         150         150         145         100         0           595       \nb           220         210         200         190         0           150         780       \nc           200         195         150         150         195         200         695       \n</code></pre>\n\n<p>Rats. In my excitement at (I think) getting the hard part to work I forgot to mention that</p>\n\n<ul>\n<li>The list of scores can be in any order</li>\n<li>You can get the club rankings with RANK()</li>\n<li>You can then pull the top 10 into another table using MATCH() and INDEX()</li>\n</ul>\n\n<pre><code>\n    A               B       C       D           E       F       G               H    \n1   club            Sc      Rank    UniqRk              Pos     Club            Score\n2   third-equal#1   80      3       79.999980   1       1       best            100  \n3   second          90      2       89.999970   2       2       second          90   \n4   third-equal#2   80      3       79.999960   3       3       third-equal#1   80   \n5   best            100     1       99.999950   4       3       third-equal#2   80   \n6   worst           70      5       69.999940   5       5       worst           70   \n\n</code></pre>\n\n<p>Columns A and B are our calculated scores, column E is the order in which clubs will be output in the final table. The other formulae are as follows:</p>\n\n<pre><code>C: =RANK(B2,$B$2:$B$6)      # what it says, with ties both getting the lower number\nD: =B2-ROW()*0.00001        # score, modified slightly to ensure uniqueness\nF: =SMALL($C$2:$C$6,E2)     # first output column, ranks including ties\nG: =INDEX($A$2:$A$6,MATCH(LARGE($D$2:$D$6,E2),$D$2:$D$6,0))\n                            # club name for position, using the modified score in D\nH: =INDEX($B$2:$B$6,MATCH(LARGE($D$2:$D$6,E2),$D$2:$D$6,0))\n                            # as G, but indexes into scores\n</code></pre>\n'}, {'answer_id': 7410198, 'author': 'drew', 'author_id': 943658, 'author_profile': 'https://Stackoverflow.com/users/943658', 'pm_score': 1, 'selected': False, 'text': '<p>What I do is lame, but it works. </p>\n\n<p>Just make a new column then insert this formula <code>=If(a1=N,b1,0)</code> where <code>A1</code> is criteria column, <code>N</code> is criteria and <code>B1</code> is in the column that you are trying to get the large from. Then I just do the large formula in another column.</p>\n\n<p>Sometimes I get all fancy and instead of rolling out a <code>N</code>, I will make it say <code>$C$1</code>, then spell out the criteria in that cell.</p>\n\n<p>The perfect answer would be to have Microsoft add in a <code>largeifs</code> (please read this Microsoft)</p>\n'}]
['https://Stackoverflow.com/questions/57927', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/3720/']