# Combining Large + If + Ignoring Duplicates

#### LordExcelalot

##### New Member
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

### Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

#### LordExcelalot

##### New Member
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.

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

#### Eric W

##### MrExcel MVP
Here's one option:

ABCDEF
1EntityIDQuarterOutcomesk=2
21592006110Quarter:20061
31592006110
4159200615
5119200612
6119200611
71242006110Result:9
8124200619
91592006210
101592006210
11159200625
12119200622
13119200621
141242006210
15124200629

</tbody>
Sheet12

Array Formulas
CellFormula
F7{=LARGE(IF(MATCH(F2&"|"&C2:C15,B2:B15&"|"&C2:C15,0)=ROW(B2:B15)-ROW(B2)+1,C2:C15),F1)}

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

##### MrExcel MVP
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.

Replies
9
Views
532
Replies
5
Views
702
Replies
2
Views
268
Replies
2
Views
688
Replies
7
Views
407

1,190,701
Messages
5,982,383
Members
439,777
Latest member
daleEH

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

### Which adblocker are you using?

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

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