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]

 

[Aladin Akyurek]

Here is how it's done. The workbook implements the suggestion made in post #8 ...
https://www.dropbox.com/s/ey2mxm0uxinniir/wixie%205%20highest%20values%20with%20multiple%20criterias%20and%20possible%20tie%20.xlsx?dl=0

 

[wixie]

Thank you for this, I'll study it

 

[wixie]

Here is how it's done. The workbook implements the suggestion made in post #8 ...
https://www.dropbox.com/s/ey2mxm0ux...ultiple criterias and possible tie .xlsx?dl=0

The formulas contains specific data range... from A2-A10 but I need to include all data from column A. Data will be changed monthly, so the amount of rows will vary. I tried to swithc it to $A:$A but didn't help :/

Any advice how to modify formula ?

 

[Aladin Akyurek]

The formulas contains specific data range... from A2-A10 but I need to include all data from column A. Data will be changed monthly, so the amount of rows will vary. I tried to swithc it to $A:$A but didn't help :/

Any advice how to modify formula ?

Define Lrow in the Name Manager as referring to:

=MATCH(9.99999999999999E+307,Sheet1!$A:$A)

Define Value in the Name Manager as referring to:

=Sheet1!$A$2:INDEX(Sheet1!$A:$A,Lrow)

Define Company in the Name Manager as referring to:

=Sheet1!$B$2:INDEX(Sheet1!$B:$B,Lrow)

Define Status in the Name Manager as referring to:

=Sheet1$C$2:INDEX(Sheet1!$C:$C,Lrow)

Define Ivec (from integer vector) as referring to:

=ROW(Value)-ROW(INDEX(Value,1,1))+1

The foregoing definitions makes the set up dynamic, i.e. self-adjusting.

The formulas now become...

In G1 just enter:

=MIN(COUNTIFS(Status,"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(Status="open",Value),ROWS($F$3:F3)))

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

=IF($F3="","",INDEX(Company,SMALL(IF(Status="open",IF(Value=$F3,Ivec)),COUNTIFS($F$3:F3,F3))))

In K1 just enter:

=MIN(COUNTIFS(Status,"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(Status="lost",Value),ROWS($J$3:J3)))

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

=IF($J3="","",INDEX(Company,SMALL(IF(Status="lost",IF(Value=$J3,Ivec)),COUNTIFS($J$3:J3,J3))))

 

[wixie]

Hmm, this is bit more higher level knowledge than I got used to :D

Shouldn't I always pick active one of the cells before i can use Name Manager? Just wondering which to pick. Tried to actiave A-columng for Lrow and it didnt end up too well.

 

[wixie]

Actually, i managed to find solution that is simpler, I just define the range from A2:A10 to A2:A999 or something

 

[Aladin Akyurek]

Actually, i managed to find solution that is simpler, I just define the range from A2:A10 to A2:A999 or something

That's ok, but it's not that difficult to manage the definitions.

1. Go to Formulas | Name Manager
2. Activate the New tab.
3. Type the name you want to define in the Name box.
4. Type the definition in the Refers to box.
5. Click OK.

Adjust the sheet name before you type in the definition.

 

[wixie]

Thank you for that advice, I need to bookmark this page. I already got the solution work but when i used real data. I found out that there are cells without data.
I thought that i Ishould add IFERROR-function to beginning of formula at G3


This is from your first post:

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

So i thought it should be like this:

=IFERROR($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))),"")

But it doesn't seem to work.

 

[Aladin Akyurek]

Thank you for that advice, I need to bookmark this page. I already got the solution work but when i used real data. I found out that there are cells without data.
I thought that i Ishould add IFERROR-function to beginning of formula at G3


This is from your first post:

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

So i thought it should be like this:

=IFERROR($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))),"")

But it doesn't seem to work.

What does the formula deliver in that case?

 

[wixie]

My office is in finnish but before my attempt to fix the formula, i get "missing"-note (PUUTTUU in finnish). when i try to fix the code, i get the popup that tells that there is error in this formula and = 1+1 should be 2, and it advices me to check the formula. And yes, i pressed CTRL+SHIFT+ENTER



In my real formula it looks this:

=JOSVIRHE($F3="";"";INDEKSI('[pipedrive_data.xlsx]deals list'!$D$2:$D$500;PIENI(JOS('[pipedrive_data.xlsx]deals list'!$L$2:$L$500="open";JOS('[pipedrive_data.xlsx]deals list'!$C$2:$C$5009=$F3;RIVI('[pipedrive_data.xlsx]deals list'!$D$2:$D$500)-RIVI('[pipedrive_data.xlsx]deals list'!$D$2)+1));LASKE.JOS.JOUKKO($F$3:F3;F3))),"")