Finding nth largest element in 2D range with duplicates

chewsters

New Member
Joined
Feb 28, 2021
Messages
2
Office Version
  1. 365
Platform
  1. MacOS
Hello, I am trying to get some help with a small project.

In short, I would like a sort of "records" page, where I can keep track of what team has scored the most points and when. All records should be distinct; in other words, if the same number of points were scored on different occasions (whether by a different team, different week, same week, etc. etc.)

Currently, this involves me finding the nth largest value within a 2D range, as well as its index (so I can find the correspondent week and team). This works fine when there are no duplicates, as my formula uses a sum-product matching to the nth largest value to locate its row and column. However, it fails for duplicates for obvious reasons.

Fantasy Ball '21.xlsx
ABCDEFGHIJKLMNOPQ
1TeamWeek 1Week 2Week 3Week 4Week 5Week 6Week 7Week 8Week 9Week 10Week 11Week 12Week 13Week 14Week 15Week 16
2A6346657785378340774747548549
3B259501325264321586416463332328
4C23509538469450621747568600382
5D121465617383568409630477201566
6E99102778291461429353714590406
7F94441606329301247691502151153
8G46551671263540539597492474391
9H92626474316544697689708614391
10I183606523255161338583544392223
11J14845047676410506295468441204
Raw PF

Cell Formulas
RangeFormula
B2:B21B2=LARGE('Raw PF'!$B$2:$Q$11,A2)
C2:C21C2=SUMPRODUCT(('Raw PF'!$B$2:$Q$11=B2)*ROW('Raw PF'!$B$2:$Q$11))-ROW('Raw PF'!$B$2:$Q$11)+1
D2:D21D2=SUMPRODUCT(('Raw PF'!$B$2:$Q$11=LARGE('Raw PF'!$B$2:$Q$11,A2))*COLUMN('Raw PF'!$B$2:$Q$11))-COLUMN('Raw PF'!$B$2:$Q$11)+1
E2:E21E2=INDEX('Raw PF'!$A$1:$Q$11,C2+1,1)
F2:F21F2=INDEX('Raw PF'!$A$1:$Q$11,1,D2+1)
Press CTRL+SHIFT+ENTER to enter array formulas.
Named Ranges
NameRefers ToCells
'Raw PF'!_FilterDatabase='Raw PF'!$A$1:$S$11E2:F21
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

chewsters

New Member
Joined
Feb 28, 2021
Messages
2
Office Version
  1. 365
Platform
  1. MacOS
I accidentally posted this before writing everything!
If there are two occasions in which the same number of points are scored, I want both recorded. So for instance, 747 points were scored by Team A on Week 8 and Team C on Week 7. I'd like both listed in whatever order.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,682
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
Cell Formulas
RangeFormula
B2:B21B2=LARGE('Raw PF'!$B$2:$Q$11,A2)
C2:C21C2=INDEX('Raw PF'!$A$2:$A$11,AGGREGATE(15,6,(ROW('Raw PF'!$A$2:$A$11)-ROW('Raw PF'!$A$2)+1)/('Raw PF'!$B$2:$Q$11=B2),COUNTIFS(B$2:B2,B2)))
D2:D21D2=INDEX('Raw PF'!$B$1:$Q$1,AGGREGATE(15,6,(COLUMN('Raw PF'!$B$1:$Q$1)-COLUMN('Raw PF'!$B$1)+1)/('Raw PF'!$B$2:$Q$11=B2)/('Raw PF'!$A$2:$A$11=C2),1))
 

Watch MrExcel Video

Forum statistics

Threads
1,127,872
Messages
5,627,389
Members
416,245
Latest member
Xterminat

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