Combining Large + If + Ignoring Duplicates

[LordExcelalot]

Hi all,

Before I write my post, please note that I have thoroughly searched the internet incl. this forum on behalf of my question and unfortunately was not able to find an answer.

The following is my status quo:

I am combining a Large and If function in the following manner:

Here's the formula in text format:
=LARGE(IF($B:$B=20061,$C:$C),$F$1)

As you can see, C:C is the range, but only if Quarter = 20061.

My problem is, that I also need to account for duplicates. I.e. the desired outcome in the example above would be 5, but since my formula does not account for duplicates, the actual outcome is 10.

Now, in my various searches on Google and on this forum, a solution to the problem I have come across a couple of times looks like this:

=LARGE(range,COUNTIF(range,MAX(range))+1)

My question is, how do I combine my formula with this formula, given that the "range" is not straightforward in my example. I have tried experimenting with it as well, but simply can't get it to work.

Would highly appreciate your help on this.

Thank you!

Best regards,
LordExcelalot

 

[LordExcelalot]

Small addition just fyi: Of course in this example, I see that I could obtain my desired result by entering k=3. But the picture shows just an excerpt of my data and in reality the number of rows with outcomes varies. Hence, I do not know k.

 

[Aladin Akyurek]

Care to post that sample in an Excel readable form along with the desired results, with no reference to any formula?

 

[Eric W]

Here's one option:

	A	B	C	D	E	F
1	EntityID	Quarter	Outcomes		k=	2
2	159	20061	10		Quarter:	20061
3	159	20061	10
4	159	20061	5
5	119	20061	2
6	119	20061	1
7	124	20061	10		Result:	9
8	124	20061	9
9	159	20062	10
10	159	20062	10
11	159	20062	5
12	119	20062	2
13	119	20062	1
14	124	20062	10
15	124	20062	9

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

Sheet12​

Array Formulas Cell Formula F7 {=LARGE(IF(MATCH(F2&"|"&C2:C15,B2:B15&"|"&C2:C15,0)=ROW(B2:B15)-ROW(B2)+1,C2:C15),F1)} <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>

I believe the expected result should be 9 based on row 8.

 

[Marcelo Branco]

Or

	A​	B​	C​	D​	E​	F​
1​	EntryID​	Quarter​	Outcomes​		Greatest​	3​
2​	159​	20061​	10​
3​	159​	20061​	10​
4​	159​	20061​	5​
5​	119​	20061​	2​
6​	119​	20061​	1​		Quarter​	20061​
7​	124​	20061​	10​		Result​	5​
8​	124​	20061​	9​
9​	159​	20062​	10​
10​	159​	20062​	10​
11​	159​	20062​	5​
12​	119​	20062​	2​
13​	119​	20062​	1​
14​	124​	20062​	10​
15​	124​	20062​	9​

Array formula in E7
=LARGE(IF($B$2:$B$100=$F$6,IF(FREQUENCY(IF($B$2:$B$100=$F$6,$C$2:$C$100),$C$2:$C$100),$C$2:$C$100)),$F$1)
Ctrl+Shift+Enter

M.