5 highest values with multiple criterias and possible tie

wixie

New Member
Joined
May 23, 2018
Messages
14
Hi, looked for forum to find answer for my question but didn't manage to find solution. I would like to find 5 top values from data, based on certain criteria. I also need way to present results, if there is a tie between 2 values, when status is same for both (for example companies: Foxtrot and India).
I need to export the data from excel to some other softwares, so visual sorting isnt option. I think that the functions for: F3 and G3 are the most important (that's why i blurred the lats 3 columns of this example).

I have tried the LARGE and SMALL functions, but my brains just explodes...

Here is an example to descibre my problem in a case that Deal status is O:
ABCDEFGHI
J
K
1ValueCompanyStatusDeals OpenDeals Lost


2123 456AlfaLostNo:ValueCompanyNo:
Value
Company
311 000BetaWon1.1


412 500CharlieOpen2.2.


5123 456DeltaLost3.3.


6200 050FoxtrotOpen4.4.


7290 321GolfOpen5.5.


8123 543HotelWon


9200 050IndiaOpen


10125 076JuliettWon




<tbody>
</tbody>
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Hi and welcome to MrExcel,

Somthing like this:


Book1
ABCDEFGHIJK
1ValueCompanyStatusDeals OpenDeals Lost
2123456AlfaLostNo:ValueCompanyNo:ValueCompany
311000BetaWon1290321Golf1123456Alfa
412500CharlieOpen2200050Foxtrot2123456Delta
5123456DeltaLost3200050India3
6200050FoxtrotOpen412500Charlie4
7290321GolfOpen55
8123543HotelWon
9200050IndiaOpen
10125076JuliettWon
Sheet1
Cell Formulas
RangeFormula
F3{=IFERROR(LARGE(IF($C$2:$C$10="Open",$A$2:$A$10,""),E3),"")}
G3{=IFERROR(INDEX($B$2:$B$10,SMALL(IF($F3=$A$2:$A$10,ROW($A$2:$A$10)-ROW($A$2)+1),IF(COUNTIFS($A$2:$A$10,F3,$C$2:$C$10,"open")>1,ROW()-1-COUNTIFS($A$2:$A$10,F3,$C$2:$C$10,"open"),COUNTIFS($A$2:$A$10,F3,$C$2:$C$10,"open")))),"")}
J3{=IFERROR(LARGE(IF($C$2:$C$10="Lost",$A$2:$A$10,""),I3),"")}
K3{=IFERROR(INDEX($B$2:$B$10,SMALL(IF($J3=$A$2:$A$10,ROW($A$2:$A$10)-ROW($A$2)+1),IF(COUNTIFS($A$2:$A$10,J3,$C$2:$C$10,"lost")>1,ROW()-COUNTIFS($A$2:$A$10,J3,$C$2:$C$10,"lost"),COUNTIFS($A$2:$A$10,J3,$C$2:$C$10,"lost")))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Thank you for this help, I tried to modify the code and needed to change , to ;
Then I tried to change that specified data range fromA2:A10 to column A, and the functions got broken. I recieved random numbers from the range and with random Company names . For example: $A$2:$A$10 I replaced with $A:$A. Is that wrong way determining data range, or did i possibly change something else to get random information ?

 
Upvote 0
Thank you for this help, I tried to modify the code and needed to change , to ;
Then I tried to change that specified data range fromA2:A10 to column A, and the functions got broken. I recieved random numbers from the range and with random Company names . For example: $A$2:$A$10 I replaced with $A:$A. Is that wrong way determining data range, or did i possibly change something else to get random information ?


Hmm i managed to notice, that if I change the values of ranged data, function breaks and I get some random company names...
 
Upvote 0
Hi,

With regards to the comma versus semicolon: The standard separator within an excel function is set by the list separator within the regional settings of windows.
As the comma within the settings for Finland is used as the decimal symbol, windows will set the semicolon as list separator.
My version is set to English hence the use of the comma.

With regards to the complete column: As these are Array functions you can't just change the range to a complete column. A complete column, however convenient in some cases, will, in most cases, have empty cells within. A empty cell does "break" the result of the formula so don't use complete columns within this function.
Don't forget: these are array functions which should be confirmed with (CTRL+Shift+Enter)
 
Last edited:
Upvote 0
Thank you for your answers, I appreciate help very much. :)

Alrighty, is there anyway to "handle" the empty cells by skipping them or something? The thing is that the data that I'll use for the functions, will be exported from other system ('ll save them as excel workbook), then I will run the the functions in separate excel work book. I can't determine the exact amount of rows because data amount varies every time.
 
Upvote 0
Hi,

Not the cleanest solution and this will absolutely have impact on calculation time if your dataset is huge but try this:


Book1
ABCDEFGHIJK
1ValueCompanyStatusDeals OpenDeals Lost
2123456AlfaLostNo:ValueCompanyNo:ValueCompany
311000BetaWon1290321Foxtrot1123456Alfa
412500CharlieOpen2290321Golf2123456Delta
5123456DeltaLost3200050India3
6290321FoxtrotOpen412500Charlie4
7290321GolfOpen55
8123543HotelWon
9200050IndiaOpen
10125076JuliettWon
Sheet3
Cell Formulas
RangeFormula
F3{=IFERROR(LARGE(IF($C:$C=RIGHT(E$1,4),$A:$A,""),E3),"")}
G3{=IFERROR(INDEX($B:$B,SMALL(IF(F3=$A:$A,ROW($A:$A)-ROW($A$2)+2),IF(COUNTIFS($A:$A,F3,$C:$C,RIGHT(E$1,4))>1,ROW()-COUNTIFS($A:$A,F3,$C:$C,RIGHT(E$1,4)),COUNTIFS($A:$A,F3,$C:$C,RIGHT(E$1,4))))),"")}
J3{=IFERROR(LARGE(IF($C:$C=RIGHT(I$1,4),$A:$A,""),I3),"")}
K3{=IFERROR(INDEX($B:$B,SMALL(IF(J3=$A:$A,ROW($A:$A)-ROW($A$2)+2),IF(COUNTIFS($A:$A,J3,$C:$C,RIGHT(I$1,4))>1,ROW()-COUNTIFS($A:$A,J3,$C:$C,RIGHT(I$1,4)),COUNTIFS($A:$A,J3,$C:$C,RIGHT(I$1,4))))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0

Book1
ABCDEFGHIJK
1ValueCompanyStatusDeals Open54Deals Lost52
2123456AlfaLostNo:ValueCompanyNo:ValueCompany
311000BetaWon1290321Golf1123456Alfa
412500CharlieOpen2200050Foxtrot2123456Delta
5123456DeltaLost3200050India
6200050FoxtrotOpen412500Charlie
7290321GolfOpen
8123543HotelWon
9200050IndiaOpen
10125076JuliettWon
Sheet1


In G1 just enter:

=MIN(COUNTIFS(C2:C10,"open"),F1)

In E3 just enter and copy down:

=IF($F3="","",ROWS($F$3:F3))

In F3 control+shift+enter, not just enter, and copy down:

=IF(ROWS($F$3:F3)>$G$1,"",LARGE(IF($C$2:$C$10="open",$A$2:$A$10),ROWS($F$3:F3)))

In G3 control+shift+enter, not just enter, and copy down:

=IF($F3="","",INDEX($B$2:$B$10,SMALL(IF($C$2:$C$10="open",IF($A$2:$A$10=$F3,ROW($B$2:$B$10)-ROW($B$2)+1)),COUNTIFS($F$3:F3,F3))))

In K1 just enter:

=MIN(COUNTIFS(C2:C10,"lost"),F1)

In I3 just enter and copy down:

=IF($J3="","",ROWS($J$3:J3))


In J3 control+shift+enter, not just enter, and copy down:

=IF(ROWS($J$3:J3)>$K$1,"",LARGE(IF($C$2:$C$10="lost",$A$2:$A$10),ROWS($J$3:J3)))

In K3 control+shift+enter, not just enter, and copy down:

=IF($J3="","",INDEX($B$2:$B$10,SMALL(IF($C$2:$C$10="lost",IF($A$2:$A$10=$J3,ROW($B$2:$B$10)-ROW($B$2)+1)),COUNTIFS($J$3:J3,J3))))
 
Upvote 0
Hmm, i seem to get NAME! -error and started wondering if I have typo somewhere. What does the number 4 mean in RIGHT - formula?
 
Upvote 0
Hi,

Not the cleanest solution and this will absolutely have impact on calculation time if your dataset is huge but try this:

ABCDEFGHIJK
1ValueCompanyStatusDeals OpenDeals Lost
2123456AlfaLostNo:ValueCompanyNo:ValueCompany
311000BetaWon1290321Foxtrot1123456Alfa
412500CharlieOpen2290321Golf2123456Delta
5123456DeltaLost3200050India3
6290321FoxtrotOpen412500Charlie4
7290321GolfOpen55
8123543HotelWon
9200050IndiaOpen
10125076JuliettWon

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet3

Array Formulas
CellFormula
F3{=IFERROR(LARGE(IF($C:$C=RIGHT(E$1,4),$A:$A,""),E3),"")}
G3{=IFERROR(INDEX($B:$B,SMALL(IF(F3=$A:$A,ROW($A:$A)-ROW($A$2)+2),IF(COUNTIFS($A:$A,F3,$C:$C,RIGHT(E$1,4))>1,ROW()-COUNTIFS($A:$A,F3,$C:$C,RIGHT(E$1,4)),COUNTIFS($A:$A,F3,$C:$C,RIGHT(E$1,4))))),"")}
J3{=IFERROR(LARGE(IF($C:$C=RIGHT(I$1,4),$A:$A,""),I3),"")}
K3{=IFERROR(INDEX($B:$B,SMALL(IF(J3=$A:$A,ROW($A:$A)-ROW($A$2)+2),IF(COUNTIFS($A:$A,J3,$C:$C,RIGHT(I$1,4))>1,ROW()-COUNTIFS($A:$A,J3,$C:$C,RIGHT(I$1,4)),COUNTIFS($A:$A,J3,$C:$C,RIGHT(I$1,4))))),"")}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Hmm can't edit my posts, i meant this solution in my recent question.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,267
Members
448,558
Latest member
aivin

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