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

Help with rank on multiple criteria

I

iamtomw

New Member
Hello kind people,

I have a dataset that includes location, name and date. I am trying produce a top 5 report of people at each location.

The locations are fixed but the people (and dates of course) will vary from month to month.

In effect I am trying to produce a single table in which I can select the month and it will show the top 5 people at each location, for that month - something like the attached, but with the names auto-generated based on the top 5 for that location.

I've been messing around with rank and index and can generate a list based on the number of times a name occurs but cannot get date and location to factor in.

I could create a pivot table for each site, but there are a lot of sites, so I'd rather have a formula that I can drag over.

Is there a way to do this in Excel? I can do it with SQL easily, but excel has me stumped and SQL isn't really an option here!

My thanks in advance for anyone taking the time to look at this :)
 

Attachments

  • Book1.xlsx
    10.3 KB · Views: 4
iamtomw said:
Hello kind people,

I have a dataset that includes location, name and date. I am trying produce a top 5 report of people at each location.

The locations are fixed but the people (and dates of course) will vary from month to month.

In effect I am trying to produce a single table in which I can select the month and it will show the top 5 people at each location, for that month - something like the attached, but with the names auto-generated based on the top 5 for that location.

I've been messing around with rank and index and can generate a list based on the number of times a name occurs but cannot get date and location to factor in.

I could create a pivot table for each site, but there are a lot of sites, so I'd rather have a formula that I can drag over.

Is there a way to do this in Excel? I can do it with SQL easily, but excel has me stumped and SQL isn't really an option here!

My thanks in advance for anyone taking the time to look at this :)
Click to expand...
Hi,

I have used PowerQuery to unpivot columns and grouped the row; loaded it as a data model to insert as a pivot in a new worksheet.

Just click on Data tab "Refresh all" to extract information.

P.S. Power Query feature is built in office 2016 dont have to download it but you can download from below if you are using office 2013 to use this feature
https://www.microsoft.com/en-gb/download/details.aspx?id=39379

Hope this helps.

Regards,
A!
 

Attachments

  • Book1.xlsx
    142.1 KB · Views: 5
You must log in or register to reply here.
Back
Top