• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

rank desired number of large value without duplicate in other table

K

kar2rost

New Member
Hello, I have tried different ways but I could not solve this problem
I have searched a lot on the internet, but it still didn't work
I ask my friends to solve this for me
I want to find the top example 30 items without duplicate data from column H and insert them into the table shown
The desired number is entered in the yellow cell
The only important point here is that at the end of each column there is an H number that should not be included in the calculations
h38 h71 h93 and etc.
 

Attachments

  • Book15.xlsx
    15.2 KB · Views: 1
Everyone says Cross posted at.....
But no one helps here and there
If any of my friends know how to solve this problem
Thankful
 
The more you cross post (which is not forbidden), the less people are willing to help, as they might loose their time. Did you take a moment to read the link provided in post #3 to understand why adding links is indispensable?
 
Having looked at the other 2 forums I can see some solutions, so I haven't wasted my time devising formula solutions that you've already got. Instead it's given me time to put together an alternative approach, Power Query, which I'm fairly sure is built-in to your version of Excel:
79913
Here, you type into the yellow cells the number of results you want to see as well as the sex and code you want to filter for. Any cell you leave blank will not be filtered for.
There's also a question regarding how you want the rank column to show; In the picture above, there are 2 rows ranked 6th, no rank 7, and one rank 8. These are showing the number of rows with amounts greater then the row's own mond value in the filtered set, plus 1.

In the picture below, the ranking is the number of unique amounts greater than the current row's own mond value in the filtered set, plus 1:
79914

There are more ways to rank!
Below, the rankings are based on the whole data set (before filtering), and again, this can be based on the number of rows or the number of distinct amounts greater than the current row's mond value:
79915

The following is not intended to be in any way nasty, but to drive home the importance of declaring where you've cross posted the same (or similar) question:
I'll attach the file and give more explanation if:
  • You can confirm that the two links provided are the only other places you've cross-posted to.
  • If there are others you supply links to them too.
  • Preference only: you supply links at the other forums (eg. MrExcel)
You can, of course (if you're indignant that I should ask such extreme things of you) completely ignore this message, in which case I'll return the compliment.
 
Last edited:
You must log in or register to reply here.
Back
Top