5 highest values with multiple criterias and possible tie

[wixie]

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

[/TD]
[/TR]
</tbody>[/TABLE]

 

[jorismoerings]

Hi and welcome to MrExcel,

Somthing like this:

Book1
	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	Golf		1	123456	Alfa
4	12500	Charlie	Open		2	200050	Foxtrot		2	123456	Delta
5	123456	Delta	Lost		3	200050	India		3
6	200050	Foxtrot	Open		4	12500	Charlie		4
7	290321	Golf	Open		5				5
8	123543	Hotel	Won
9	200050	India	Open
10	125076	Juliett	Won
Sheet1

Cell Formulas
Range	Formula
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.

 

[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 ?

 

[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...

 

[jorismoerings]

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)

 

[wixie]

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.

 

[jorismoerings]

Hi,

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

Book1
	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

Cell Formulas
Range	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))))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Aladin Akyurek]

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

 

[wixie]

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

 

[wixie]

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
Value	Company	Status	Deals Open	Deals Lost
Alfa	Lost	No:	Value	Company	No:	Value	Company
Beta	Won	Foxtrot	Alfa
Charlie	Open	Golf	Delta
Delta	Lost	India
Foxtrot	Open	Charlie
Golf	Open
Hotel	Won
India	Open
Juliett	Won

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]123456[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]11000[/TD]

[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]290321[/TD]

[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]123456[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]12500[/TD]

[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]290321[/TD]

[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]123456[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]123456[/TD]

[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]200050[/TD]

[TD="align: right"][/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]290321[/TD]

[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]12500[/TD]

[TD="align: right"][/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]290321[/TD]

[TD="align: right"][/TD]
[TD="align: right"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]123543[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]200050[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]125076[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F3[/TH]
[TD="align: left"]{=IFERROR(LARGE(IF($C:$C=RIGHT(E$1,4),$A:$A,""),E3),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G3[/TH]
[TD="align: left"]{=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))))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J3[/TH]
[TD="align: left"]{=IFERROR(LARGE(IF($C:$C=RIGHT(I$1,4),$A:$A,""),I3),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]K3[/TH]
[TD="align: left"]{=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))))),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

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