Returning top 3 list with duplicates using INDEX, MATCH, IF, SMALL & TEXTJOIN

Dlloyd15

New Member
Joined
Feb 20, 2020
Messages
7
Office Version
365
Platform
Windows
Hi all,

Firstly I'd just like to say I'm fairly new at using extensive functions in Excel, so if I've missed anything or am doing this the completely wrong way then apologies, still trying to get my head round everything.

So my problem:
I have a table with first name, last name, office, and (car) CO2 emissions and am trying to get the name of the employees with the 3 best (lowest) CO2 emissions per office, in this case = GS. But am trying to keep everything tidy so have one big formula in a cell doing all the functions simultaneously. So far I have been able to do this but have hit a block because the 3rd best (lowest) employee is joint 3rd with 108 g/km and as a result the cell is only showing one name rather than the two.

Does anyone know what i need to do to fix this, or if it is possible? Formula and tables below. Hopefully this all makes sense but happy to answer any questions if not/it helps.

Thanks in advance!

(P.S. all names have been changed)

(Array) Formula:

{=(TEXTJOIN(",",TRUE,INDEX($B$11:$E$77,MATCH(1,($E$11:$E$77=SMALL(IF($D$11:$D$77="GS",$E$11:$E$77),3))*($D$11:$D$77="GS"),0),2)))}

This returns 'Ellis' but I need it to return 'Ellis,Parker' as both are the 3rd best CO2 emissions in the 'GS' office.

Table:
First NameLast NameOfficeCo2 Emission
OliverCaseyIN157
AidaCrawfordGS214
WilliamEllisGS108
NatalieArmstrongMD119
RosieBakerMD89
StellaBrooksIN105
OscarParkerGS108
NatalieMorrisIN115
AlenGibsonGS105
BriannaEvansMD111
AmberJonesNT133
MirandaArmstrongNT104
BrookeMasonGS133
MyraHillGS165
RyanHillMD117
RafaelGrantMD117
LuciaHillIN256
JuliaMasonGS128
EdwardBaileyWW169
AidenMurrayWW119
LilyReedLE112
KellanGibsonIN0
DarylSullivanIN138
DexterScottGS99
MartinRobertsMD127
MichelleEllisIN209
JustinWrightIN91
BradAllenGS120
RichardEllisIN134
ConnieHarperIN106
ValeriaBrownIN106
BrionyPerryGS124
CarlMorrisIN120
CarlosAdamsIN153
AprilClarkGS113
BrookeAlexanderIN129
BriannaColeIN146
PenelopeChapmanIN294
KevinDavisWW103
PaigeMitchellGS112
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,637
Office Version
365
Platform
Windows
Hi & welcome to MrExcel.
How about
=TEXTJOIN(",",TRUE,IF(($E$11:$E$77=SMALL(IF($D$11:$D$77="GS",$E$11:$E$77),3))*($D$11:$D$77="GS"),C11:C77,""))
 

Dlloyd15

New Member
Joined
Feb 20, 2020
Messages
7
Office Version
365
Platform
Windows
Thanks Fluff! I've spent some time trying to teach myself this and then spent a lot of time pulling my hair out trying to figure out how to get this to work, and you've just solved it easily! Thank you!

Never been so grateful to an English Rugby supporter ;) Good luck for the weekend, hopefully you beat Ireland and we beat the French to keep it an open contest!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,637
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback.
The weekend will definitely be interesting. Probably too close to call for any of the games.
 

Dlloyd15

New Member
Joined
Feb 20, 2020
Messages
7
Office Version
365
Platform
Windows
Just to follow up on this, I now have 5 employees with 0g/km for CO2 emissions and the same names appear in all top 3 lists, i.e its saying they're all 1st, 2nd and 3rd. Any idea on how to solve this?

Will be very interesting. I would normally back England and Wales at home but it will be very close.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
43,481
Office Version
365
Platform
Windows
Welcome to the MrExcel board!

If you have the FILTER function in your 365 version then another option would be

=TEXTJOIN(",",TRUE,FILTER(C11:C50,(D11:D50="GS")*(E11:E50=AGGREGATE(15,6,E11:E50/(D11:D50="GS"),3))))

Just to follow up on this, I now have 5 employees with 0g/km for CO2 emissions and the same names appear in all top 3 lists, i.e its saying they're all 1st, 2nd and 3rd. Any idea on how to solve this?
I was just about to ask about this sort of thing. How to solve depends on what results you want to show,

What results for 1st, 2nd and 3rd would you want, and where for this set of data

AA_MrExcel 20 02 20.xlsm
CDE
10Last NameOfficeCo2 Emission
11Name1GS0
12Name2GS100
13Name3GS105
14Name4GS100
15Name5GS110
3 lowest (2)
 

Dlloyd15

New Member
Joined
Feb 20, 2020
Messages
7
Office Version
365
Platform
Windows
Thanks Peter! Unfortunately I'm not able to use the FILTER function.

I assume the function I'm using (thanks to Fluff) is counting the joint numbers then skipping to the next non-joint number, i.e. if the top 5 are on 0g/km then the next rank would be 6 instead of 2, 3, 4, 5 and 6.
Would I be able to use COUNTIF here?

In your example I would like to show that Name1 is 1st rank, Name2 and Name4 are 2nd and then Name 3 is 3rd. But what I'm getting is Name1 is 1st, Name2 and Name4 are 2nd and 3rd.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,637
Office Version
365
Platform
Windows
And what would you want to show for something like

ZFluff.xlsm
ABCDEFG
1
2
3
4
5
6
7
8GS
91Crawford,Parker,Mason,Hill
10First NameLast NameOfficeCo2 Emission2Crawford,Parker,Mason,Hill
11OliverCaseyIN7573Crawford,Parker,Mason,Hill
12AidaCrawfordGS0
13WilliamEllisGS108
14NatalieArmstrongMD119
15RosieBakerMD89
16StellaBrooksIN105
17OscarParkerGS0
18NatalieMorrisIN115
19AlenGibsonGS105
20BriannaEvansMD111
21AmberJonesNT133
22MirandaArmstrongNT104
23BrookeMasonGS0
24MyraHillGS0
25RyanHillMD117
26RafaelGrantMD117
27LuciaHillIN256
28JuliaMasonGS128
29EdwardBaileyWW169
30AidenMurrayWW119
31LilyReedLE112
32KellanGibsonIN0
33DarylSullivanIN138
34DexterScottGS99
35MartinRobertsMD127
Data
Cell Formulas
RangeFormula
G9:G11G9=TEXTJOIN(",",TRUE,IF(($E$11:$E$77=SMALL(IF($D$11:$D$77=$G$8,$E$11:$E$77),$F9))*($D$11:$D$77="GS"),$C$11:$C$77,""))
 

Dlloyd15

New Member
Joined
Feb 20, 2020
Messages
7
Office Version
365
Platform
Windows
Thanks Fluff! So in your example

1 - Crawford, Parker, Mason, Hill
2 - Scott
3 - Gibson

Would be what I'm trying to achieve
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,637
Office Version
365
Platform
Windows
I had a horrible suspicion you would say that, if you are happy with a couple of helpers.

ZFluff.xlsm
ABCDEFGH
1
2
3
4
5
6
7
8GS
91Crawford,Parker,Mason,Hill
10First NameLast NameOfficeCo2 Emission8Scott
11OliverCaseyIN75711Gibson
12AidaCrawfordGS01
13WilliamEllisGS10815
14NatalieArmstrongMD119
15RosieBakerMD89
16StellaBrooksIN105
17OscarParkerGS01
18NatalieMorrisIN115
19AlenGibsonGS10511
20BriannaEvansMD111
21AmberJonesNT133
22MirandaArmstrongNT104
23BrookeMasonGS01
24MyraHillGS01
25RyanHillMD117
26RafaelGrantMD117
27LuciaHillIN256
28JuliaMasonGS12829
29EdwardBaileyWW169
30AidenMurrayWW119
31LilyReedLE112
32KellanGibsonIN0
33DarylSullivanIN138
34DexterScottGS998
35MartinRobertsMD127
36MichelleEllisIN209
37JustinWrightIN91
38BradAllenGS12025
39RichardEllisIN134
40ConnieHarperIN106
41ValeriaBrownIN106
42BrionyPerryGS12427
43CarlMorrisIN120
44CarlosAdamsIN153
45AprilClarkGS11319
46BrookeAlexanderIN129
47BriannaColeIN146
48PenelopeChapmanIN294
49KevinDavisWW103
50PaigeMitchellGS11217
Data
Cell Formulas
RangeFormula
G9G9=SMALL(F$11:F$77,1)
H9:H11H9=TEXTJOIN(",",1,IF(F$11:F$50=G9,C$11:C$50,""))
G10G10=AGGREGATE(15,6,F$11:F$50/(F$11:F$50<>G9),1)
G11G11=AGGREGATE(15,6,F$11:F$50/(F$11:F$50<>G10)/(F$11:F$50<>G9),1)
F11:F50F11=IF(D11=H$8,COUNTIF(E$11:E$77,"<"&E11)+1,"")


Otherwise I cannot help.
 

Forum statistics

Threads
1,089,514
Messages
5,408,722
Members
403,223
Latest member
rholmesa

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top