View original (French)

Create a Google Sheets Top/Flop

Solved
n_6248 Posted messages 8 Status Member -  
n_6248 Posted messages 8 Status Member -
Hello,

Here is my problem, I want to create a top 5 of the services that I perform.

So I am using the formula in cell B3

=LARGE('Détail Tableau de bord'!C:C,A3)

and the formula in C3

=INDEX('Détail Tableau de bord'!F:F,MATCH($B3,'Détail Tableau de bord'!$E:$E,0))

Unfortunately, in column B where the searched values are displayed, the same value appears twice, causing my Index formula to display the same information twice.

How can I make my Index formula account for duplicates and not display the same value?

Thank you in advance

Best regards

Configuration: Windows / Chrome 85.0.4183.121

8 answers

  1.
    Raymond PENTIER Posted messages 58219 Registration date   Status Contributor Last intervention   17 485
     
    If the same value appears twice, the software can only display the same result...

    --
    Retirement is great! Especially in the Caribbean...
    Raymond (INSA, AFPA)
    0
  2. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
     
    Hello
    example with a matrix formula, to be entered by pressing the enter key while holding down the ctrl and shift keys
    it automatically appears in curly braces in the formula bar with the limitation of fields as it is quite heavy and the fields must start in row 1 to avoid an error (as it works with the row numbers of the sheet, not the field:
    =IFERROR(INDEX('Dashboard Detail'!$F$1:$F$1000;SMALL(IF('Dashboard Detail'!$E$1:$E$1000=$B$3;ROW($A$1:$A$1000));ROW(A1)));"")
    to drag down to the necessary height according to the maximum number of anticipated results
    be careful with the $ signs in the right places
    best regards
    ps: a tip < name the fields with short names to shorten the formulas!

    0
  3. Raymond PENTIER Posted messages 58219 Registration date   Status Contributor Last intervention   17 485
     
    It is always very difficult and random to provide a formula regarding a table that we do not have!
    If you want a precise answer, send your Excel file, modifying any confidential information. 
     1) Go to https://www.cjoint.com/ 
     2) Click on [Browse] to select your file (max 15 MB)
     3) Scroll down to click on the blue button [Create Cjoint link] 
     4) After a few seconds, the second page will display, with the link in bold; right-click on it and choose "Copy link"
     5) Go back to your discussion on CCM, and in your message, paste it.
    =>See the document https://www.commentcamarche.net/faq/29493-utiliser-cjoint-pour-heberger-des-fichiers 
    There are also: 
     1) https://mon-partage.fr/ 
     2) https://www.transfernow.net/

    --
    Retirement is great! Especially in the Antilles...
    Raymond (INSA, AFPA)
    0
  4. n_6248 Posted messages 8 Status Member
     
    https://www.cjoint.com/c/JJfhBWHx57L

    Hello everyone,

    First of all, I want to thank you for your responses.
    Vaucluse, I tried with your formula but unfortunately I couldn't get it to work, I don't know if it's because I miswrote the formula.

    I agree with you Raymond, it's clearer with a file, so I'm attaching the file I am working on.

    Best regards.
    0
  6. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
     
    Hello
    your file in return, it needs a variant at the end of the matrix to extract the correct value
    see the annotations in red
    the range processed in the Detail sheet goes from row 2 to row 1000
    get back to me if there is a problem
    https://mon-partage.fr/f/oSDAG5LV/
    best regards

    --
    The quality of the response mostly depends on the clarity of the question, thank you!
    0
  7. n_6248 Posted messages 8 Status Member
     
    Hello Vaucluse,

    Thank you very much for your help, unfortunately I can't open your file as Chrome is blocking the address.
    0
  8. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
     
    so let's try here: (the same site as you)
    https://www.cjoint.com/c/JJfjsxpwd1q
    bye

    --
    The quality of the answer mainly depends on the clarity of the question, thank you!
    0
  9. n_6248 Posted messages 8 Status Member
     
    It's working very well, thank you so much!
    0