Likes:  0

Thread: 5 highest values with multiple criterias and possible tie

1. 5 highest values with multiple criterias and possible tie

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:
 A B C D E F G H I J K 1 Value Company Status Deals Open Deals Lost 2 123 456 Alfa Lost No: Value Company No: Value Company 3 11 000 Beta Won 1. 1 4 12 500 Charlie Open 2. 2. 5 123 456 Delta Lost 3. 3. 6 200 050 Foxtrot Open 4. 4. 7 290 321 Golf Open 5. 5. 8 123 543 Hotel Won 9 200 050 India Open 10 125 076 Juliett Won

2. Re: 5 highest values with multiple criterias and possible tie

Hi and welcome to MrExcel,

Somthing like this:

ABCDEFGHIJK
1ValueCompanyStatusDeals OpenDeals Lost
2123456AlfaLostNo:ValueCompanyNo:ValueCompany
311000BetaWon1290321Golf1123456Alfa
412500CharlieOpen2200050Foxtrot2123456Delta
5123456DeltaLost3200050India3
6200050FoxtrotOpen412500Charlie4
7290321GolfOpen55
8123543HotelWon
9200050IndiaOpen
10125076JuliettWon

Sheet1

Array Formulas
CellFormula
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")))),"")}
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

3. Re: 5 highest values with multiple criterias and possible tie

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 ?

4. Re: 5 highest values with multiple criterias and possible tie

Originally Posted by wixie
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...

5. Re: 5 highest values with multiple criterias and possible tie

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)

6. Re: 5 highest values with multiple criterias and possible tie

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.

7. Re: 5 highest values with multiple criterias and possible tie

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

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))))),"")}
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

8. Re: 5 highest values with multiple criterias and possible tie

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))))

9. Re: 5 highest values with multiple criterias and possible tie

Hmm, i seem to get NAME! -error and started wondering if I have typo somewhere. What does the number 4 mean in RIGHT - formula?

10. Re: 5 highest values with multiple criterias and possible tie

Originally Posted by jorismoerings
Hi,

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

A B C D E F G H I J K
1 Value Company Status Deals Open Deals Lost
2 123456 Alfa Lost No: Value Company No: Value Company
3 11000 Beta Won 1 290321 Foxtrot 1 123456 Alfa
4 12500 Charlie Open 2 290321 Golf 2 123456 Delta
5 123456 Delta Lost 3 200050 India 3
6 290321 Foxtrot Open 4 12500 Charlie 4
7 290321 Golf Open 5 5
8 123543 Hotel Won
9 200050 India Open
10 125076 Juliett Won
Sheet3

Array Formulas
Cell Formula
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))))),"")}
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself
Hmm can't edit my posts, i meant this solution in my recent question.