Copy Name and Sum total scores to new Tab

SirScott13

Board Regular
Joined
Sep 21, 2012
Messages
63
Office Version
  1. 365
Platform
  1. Windows
I have a spreadsheet that is compiling the results of 6 different climbing competitions. The climbers can attend all 6 competitions and can score multiple times per competition, but only their top 2 point totals count towards their season rankings. I have a sheet which groups all of the competitions into a sheet and then lists their top 2 scores. At this point, I just want to create a new tab/sheet which ranks the athletes with the sum of their top two climbs. This list would then be ranked from highest point total to lowest.

Here is the Mini-Sheet for the existing data.

Climbing Rankings 2021.xlsx
ABC
3FJR
4RankNamePoints
51Steve Williams100
62Bill Gates100
73**** Trickle80
84Bob Barker65
95Jacob Degrom35
106Jacob Degrom100
117Jacob Degrom110
128David Johnson40
139Jason Johnson30
1410**** Trickle40
1511**** Trickle42
161200
171300
181400
191500
201600
211700
221800
231900
242000
252100
262200
272300
282400
292500
302600
312700
322800
332900
343000
353100
363200
373300
383400
393500
403600
413700
423800
433900
444000
451Jason Johnson80
46200
47300
48400
49500
50600
51700
52800
53900
541000
551100
561200
571300
581400
591500
601600
611700
621800
631900
642000
652100
662200
672300
682400
692500
702600
712700
722800
732900
743000
753100
763200
773300
783400
793500
803600
813700
823800
833900
844000
85100
86200
87300
88400
89500
90600
91700
92800
93900
941000
951100
961200
971300
981400
991500
1001600
1011700
1021800
1031900
1042000
1052100
1062200
1072300
1082400
1092500
1102600
1112700
1122800
1132900
1143000
1153100
1163200
1173300
1183400
1193500
1203600
1213700
1223800
1233900
1244000
125100
126200
127300
128400
129500
130600
131700
132800
133900
1341000
1351100
1361200
1371300
1381400
1391500
1401600
1411700
1421800
1431900
1442000
1452100
1462200
1472300
1482400
1492500
1502600
1512700
1522800
1532900
1543000
1553100
1563200
1573300
1583400
1593500
1603600
1613700
1623800
1633900
1644000
1651Steve Williams65
1662Bill Gates35
1673Bob Barker100
1684David Johnson1000
169500
170600
171700
172800
173900
1741000
1751100
1761200
1771300
1781400
1791500
1801600
1811700
1821800
1831900
1842000
1852100
1862200
1872300
1882400
1892500
1902600
1912700
1922800
1932900
1943000
1953100
1963200
1973300
1983400
1993500
2003600
2013700
2023800
2033900
2044000
2051Jason Johnson100
206200
207300
208400
209500
210600
211700
212800
213900
2141000
2151100
2161200
2171300
2181400
2191500
2201600
2211700
2221800
2231900
2242000
2252100
2262200
2272300
2282400
2292500
2302600
2312700
2322800
2332900
2343000
2353100
2363200
2373300
2383400
2393500
2403600
2413700
2423800
2433900
2444000
245
246Name1st Score2nd Score
247Steve Williams10065
248Bill Gates10035
249**** Trickle8042
250Bob Barker10065
251Jacob Degrom110100
252David Johnson100040
253Jason Johnson10080
Consolidated Data
Cell Formulas
RangeFormula
B6:B8,A13:C44,C5:C12,A5:A12B6='High Point'!B6
A45:C84A45=Ozark!A5
A85:C124A85='Upper Limits'!A5
A125:C164A125=RoKC!A5
A165:C204A165='The Crag'!A5
A205:C243,A244:B244A205='High Point Cleveland'!A5
C244C244=A14:C244='High Point Cleveland'!C44
A247:A253A247=UNIQUE(FILTER(B5:B244,B5:B244<>0))
B247:C253B247=INDEX(SORT(FILTER($C$2:$C$244,$B$2:$B$244=A247),,-1),{1,2})
Dynamic array formulas.
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Oh, one last thing. The list of names in the consolidated list grows as a name is added, but the scores do not automatically grow. I can extend the formula for 40-50 additional rows, but it then populates with unexpected data. Until a name exists, it just spits out the number 140 in the "1st Score" column.
 
Upvote 0

Forum statistics

Threads
1,223,237
Messages
6,170,930
Members
452,367
Latest member
TePunaBloke

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
Back
Top