1st, 2nd, 3rd places or more than one in either position.

Detectiveclem

Active Member
Joined
May 31, 2014
Messages
272
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
Hi,

I am using the following formula to place individuals 1st, 2nd or 3rd depending upon their score.

=IFERROR(OFFSET($G5,MATCH(LARGE(K5:K17,3),K5:K17,0)-1,0),"")

All works well, until I have two or more people with the same top score, I.E. 1st place is Annabel with 45, then John with 43 is second, however if Adnan also has 43 points, then my formula simply show Annabel is 1st and then John as 2nd and also 3rd place, no mention of Adnan. Can anyone assist me so that I have simply 1st and 2x 2nds (these seconds could simply be shown together) in this scenario and then a 3rd with the next score down (or wherever the the duplication appears).

I hope I have explained my issue
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

smitpau

Board Regular
Joined
Apr 2, 2020
Messages
139
Office Version
  1. 365
Platform
  1. Windows
If you use Office 365 try the Index / Sort / Sequence functions, this link should give hopefully the necessary info.


Let me know if it doesn't work though.

Thanks,
 

Detectiveclem

Active Member
Joined
May 31, 2014
Messages
272
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
If you use Office 365 try the Index / Sort / Sequence functions, this link should give hopefully the necessary info.


Let me know if it doesn't work though.

Thanks,
Hi Smit-au,

Thanks for assisting, but not sure how I would write such a formula, having never used Index/Sort/Sequence. I am using Office 365.

Can you assist please?
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
I'm not sure how you have your data.
But assuming the following, we have a triple tie in second and also another tie; and all names are displayed:

Dante Amor
AGKLM
4NameScoreName
5Ana43Fra
6Ben40Ism
7Car43Car
8Dan20Ana
9Ern19Ben
10Fra50Noe
11Gus15Mia
12Hec18Len
13Ism43Dan
14Jho19Jho
15Len21Ern
16Mia25Hec
17Noe38Gus
Hoja5
Cell Formulas
RangeFormula
M5:M17M5=INDEX($G$5:$G$17,SUMPRODUCT((LARGE($K$5:$K$17+(ROW($K$5:$K$17)/1000),ROWS(N$5:N5))=$K$5:$K$17+(ROW($K$5:$K$17)/1000))*(ROW($K$5:$K$17)))-4)
 

Detectiveclem

Active Member
Joined
May 31, 2014
Messages
272
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

Thank you DantaAmor,

This doesn't quite achieve what I am trying to do. As you'll see when I have two groups with the same score the one on the top of the list when they share second place with another they appear twice rather than allowing the other person with the same score to be shown. The following screenshot will show you what the issue is.

Screenshot 2020-05-13 11.01.49.png
 

smitpau

Board Regular
Joined
Apr 2, 2020
Messages
139
Office Version
  1. 365
Platform
  1. Windows
To use DanteAmor's example this code would work

=INDEX(SORT(C6:D18,2,-1),SEQUENCE(10))

In your case if you use Office 365 this should work if you drag it across the columns.

=INDEX(SORT($A$2:B$13,1,-1),SEQUENCE(3))
 

Detectiveclem

Active Member
Joined
May 31, 2014
Messages
272
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

Thanks, but how should the formula look like in my case as the name range is G5:G17, the Scores are in each of the columns adjacent to Column G and my formal =IFERROR(OFFSET($G5,MATCH(LARGE(H5:H17,1),H5:H17,0)-1,0),"") appears in cells
H21 etc for 1st place and
=IFERROR(OFFSET($G5,MATCH(LARGE(H5:H17,2),H5:H17,0)-1,0),"")
for 2nd place in H22 etc and
=IFERROR(OFFSET($G5,MATCH(LARGE(H5:H17,3),H5:H17,0)-1,0),"")
for 3rd place in H23

Thanks again.

Paul
 

smitpau

Board Regular
Joined
Apr 2, 2020
Messages
139
Office Version
  1. 365
Platform
  1. Windows
Which version of Excel do you use?
 

smitpau

Board Regular
Joined
Apr 2, 2020
Messages
139
Office Version
  1. 365
Platform
  1. Windows
Try this in cell H21 and see if it works dragging across to the right again.

=INDEX(SORT($G$5:H$17,2,-1),SEQUENCE(3))

The 2 may need to change to 3 etc for the 3 column so I in this case.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,359
Messages
5,601,153
Members
414,431
Latest member
JustmemyselfandI

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