Share via

FORMULA USING LARGE FUNCTION WITH DUPLICATE VALUES

Anonymous
2019-07-09T19:06:18+00:00

Hello,

I need to pull the top 10 values but only issue is I have duplicates in the column.  Therefore the top 10 values wont pull in correctly. For example 3 and 4 will be the same number. 

Is there a formula I can add to the LARGE function which will ignore the duplicate values and pull the next largest in the column (it must be the top 10)?

Thank you!

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2019-07-10T06:31:41+00:00

    Enter below formula as an array formula (Ctrl+Shift+Enter) in cell D2 and copy down till D11 - this returns the Largest 10 values from column B:

    =IFERROR(LARGE(IF(FREQUENCY(IF(B$2:B$20<>"",MATCH(B$2:B$20,B$2:B$20,0)),ROW(B$2:B$20)-ROW(B$2)+1), B$2:B$20), ROW(B1)),"")

    Enter below formula as an array formula (Ctrl+Shift+Enter) in cell E2 and copy down till E11 - this returns the Largest 10 values from column B where column A = 45454:

    =IFERROR(LARGE(IF(FREQUENCY(IF(A$2:A$20=45454,MATCH(B$2:B$20,B$2:B$20,0)),ROW(B$2:B$20)-ROW(B$2)+1),B$2:B$20),ROW(B1)),"")

    Regards,

    Amit Tandon

    www.excelanytime.com

    http://twitter.com/AmitTandonExcel

    2 people found this answer helpful.
    0 comments

7 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  2. Anonymous
    2019-07-09T19:28:33+00:00

    For example:

    C2=MAX(A2:A9)

    C3=MAX(IF($A$2:$A$9<C2,$A$2:$A$9))

    Drop down the formula will works.

    0 comments
  3. Anonymous
    2019-07-09T19:51:07+00:00

    Thanks it seems to be working but I have one more item to add.

    I have account numbers in column P.  For example, a box in column P with account # 45454.  I need it to pull the top 10 amounts for that account only (45454. 

    With the previous formula you provided it will pull top 10 amounts in the entire column.  The amounts in this column are pulling from all difference account numbers.  Just need it to separate and pull top 10 for each account number.

    0 comments