Excel Problem on Duplicate values

%3CLINGO-SUB%20id%3D%22%5C%26quot%3Blingo-sub-3161892%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3EExcel%20Problem%20on%20Duplicate%20values%26lt%3B%5C%2Flingo-sub%26gt%3B%3CLINGO-BODY%20id%3D%22%5C%26quot%3Blingo-body-3161892%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CP%3EGood%20day%2C%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3EI%20want%20to%20obtain%20the%20top%203%20highest%20scores%20in%20the%20table%20below%20without%20duplications.%20In%20this%20example%2C%20the%20correct%20order%20should%20be%20Numeric%20Skills%20(B18)%20-%20100%25%3B%20Microsoft%20Word%20(B25)%20-%20100%25%3B%20Excel%20Skills%20(B24)%20-%2096%25.%20The%20same%20goes%20for%20the%20lowest%20scores%20obtained%20in%20the%20test%2C%20in%20other%20words%3A%20Legal%20Aspects%20(40%25)%3B%20Managing%20a%20small%20business%20(50%25)%20and%20Cost%20Accounting%20(60%25).%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%5C%26quot%3Blia-inline-image-display-wrapper%22%20lia-image-align-inline%3D%22%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Fgxcuf89792%2F%5C%26quot%3Bhttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F347923i67633CC28ACEB6C7%2Fimage-dimensions%2F590x301%3Fv%3Dv2%5C%26quot%3B%22%20width%3D%22%5C%26quot%3B590%5C%26quot%3B%22%20height%3D%22%5C%26quot%3B301%5C%26quot%3B%22%20role%3D%22%5C%26quot%3Bbutton%5C%26quot%3B%22%20title%3D%22Zellie1704_0-1644839076374.png%22%20alt%3D%22%5C%26quot%3BZellie1704_0-1644839076374.png%5C%26quot%3B%22%20%2F%3E%26lt%3B%5C%2Fspan%26gt%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%26lt%3B%5C%2Flingo-body%26gt%3B%3CLINGO-LABS%20id%3D%22%5C%26quot%3Blingo-labs-3161892%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%26lt%3B%5C%2Flingo-label%26gt%3B%26lt%3B%5C%2Flingo-labs%26gt%3B%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3C%2FLINGO-SUB%3E
New Contributor

Good day,

 

I want to obtain the top 3 highest scores in the table below without duplications. In this example, the correct order should be Numeric Skills (B18) - 100%; Microsoft Word (B25) - 100%; Excel Skills (B24) - 96%. The same goes for the lowest scores obtained in the test, in other words: Legal Aspects (40%); Managing a small business (50%) and Cost Accounting (60%).

 

Zellie1704_0-1644839076374.png

 

4 Replies

Hi @Zellie1704 

 

One way with Excel Web/2021/365

Screenshot.png

 

in E3

=LET(
    ScoresDesc, SORT(UNIQUE(Score),,-1),
    XLOOKUP( INDEX(ScoresDesc,SEQUENCE(MIN(ROWS(ScoresDesc),3))), Score, Skills,, 0)
)

in F3

=LET(
    ScoresAsc, SORT(UNIQUE(Score)),
    XLOOKUP( INDEX(ScoresAsc,SEQUENCE(MIN(ROWS(ScoresAsc),3))), Score, Skills,, 0)
)
Hi L.z,

Thank you for your input, is it possible to explain how the the LET function / formula works? I get an Alert message from excel that the first argument of LET must be a valid name? Also please note I'm using a Mac, which means I need to use ; and not , Not sure if this is a new to Excel or only applicable to Mac users.

@Zellie1704 The semi-colon is not Mac specific. It depends on your local settings. That aside, have a look at the attached file. It should "translate" into your local format automatically. And I believe that these formulae do what you ask for. They allow for duplicate scores but they will not duplicate descriptions descriptions.

Riny_van_Eekelen_0-1644850726196.png

 

@Zellie1704 

As @Riny_van_Eekelen mentioned this isn't Mac specific. You probably get the message "The first argument of LET must be a valid name" because your formula separator (based on your regional settings) isn't the comma but probably ;. So replace all , with ; in the suggested formulas and this should work

 

Re. LET. It's not mandatory as the following formula for the Highest would work as well

=XLOOKUP(INDEX(SORT(UNIQUE(Score),,-1),SEQUENCE(MIN(ROWS(UNIQUE(Score)),3))),Score,Skills,,0)

However, notice that UNIQUE(Score) is done (calc.) twice in the above formula. Using LET we avoid doing twice the same thing