+ Reply to Thread
Results 1 to 16 of 16

Rank data and output data based on rank

  1. #1
    Registered User
    Join Date
    07-16-2017
    Location
    norway
    MS-Off Ver
    365
    Posts
    46

    Question Rank data and output data based on rank

    Hey,
    I need help to solve a ranking and output data based on the ranking.
    I made a excel attachment with descriptions and some data as an example.

    Thanks for any help!

    EDIT:
    New updated file in attachment
    Attached Files Attached Files
    Last edited by thedrinkerparadox; 07-31-2017 at 12:08 PM.

  2. #2
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,359

    Re: Rank data and output data based on rank

    what are the limits for rank 1, 2 & 3?

  3. #3
    Registered User
    Join Date
    07-16-2017
    Location
    norway
    MS-Off Ver
    365
    Posts
    46

    Re: Rank data and output data based on rank

    Quote Originally Posted by tim201110 View Post
    what are the limits for rank 1, 2 & 3?
    Not sure what you mean.
    There might be much more data like top 10 rank.

  4. #4
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,359

    Re: Rank data and output data based on rank

    for example
    Date City 1 City 2 City 3 City 4 City 5 City 6 City 7
    15.01.2017 0,17% -0,67% 0,88% 0,77% 0,92% 0,35% 0,71%
    here we have 7 cities, 7 different values and need to define just 3 ranks.
    to calculate them we need limits for values

  5. #5
    Registered User
    Join Date
    07-16-2017
    Location
    norway
    MS-Off Ver
    365
    Posts
    46

    Re: Rank data and output data based on rank

    Okey.
    In the original data there will be like over 100 city data to rank by top 10.
    So there will be some limits.

  6. #6
    Registered User
    Join Date
    07-16-2017
    Location
    norway
    MS-Off Ver
    365
    Posts
    46

    Re: Rank data and output data based on rank

    Quote Originally Posted by tim201110 View Post
    for example
    Date City 1 City 2 City 3 City 4 City 5 City 6 City 7
    15.01.2017 0,17% -0,67% 0,88% 0,77% 0,92% 0,35% 0,71%
    here we have 7 cities, 7 different values and need to define just 3 ranks.
    to calculate them we need limits for values
    I uploaded a new updated file in the first post where I used the code I got in another post:
    https://www.excelforum.com/excel-for...not-value.html
    and
    https://www.excelforum.com/excel-for...f-located.html

  7. #7
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Rank data and output data based on rank

    Please add the new file to the forum in this threat if you need help on this question.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  8. #8
    Registered User
    Join Date
    07-16-2017
    Location
    norway
    MS-Off Ver
    365
    Posts
    46

    Re: Rank data and output data based on rank

    Here is the file in attachment
    Attached Files Attached Files

  9. #9
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Rank data and output data based on rank

    Maybe something like this in a pivot table.

    See the attached file.

  10. #10
    Registered User
    Join Date
    07-16-2017
    Location
    norway
    MS-Off Ver
    365
    Posts
    46

    Re: Rank data and output data based on rank

    Thanks oeldere.

    I am however not sure if I can use this format this way. I will have to do some checking.

    Is there a way to do this with formulas and make it more in the format output closer to the way I set it up in the example ?

  11. #11
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,208

    Re: Rank data and output data based on rank

    This proposed solution employs three helper columns, which may be hidden for aesthetic purposes, on the 'percent' sheet populated by the following array entered formula*:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The blue table on the 'rank' sheet is then populated by the formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    There are ten more helper columns on the 'rank' sheet divided into two tables, yellow and green.
    The formula that populates the green table is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The formula that populates the yellow table is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The formula that populates the 'output' sheet is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Note: the 'output' sheet formula only populates the rows headed 'Insert' and 'let og' as I don't understand the criteria for populating the rows headed 'keep' (please provide me with more information).
    Zero values in the 'output' table are hidden using conditional formatting.
    *Array entered formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  12. #12
    Registered User
    Join Date
    07-16-2017
    Location
    norway
    MS-Off Ver
    365
    Posts
    46

    Re: Rank data and output data based on rank

    Quote Originally Posted by JeteMc View Post
    This proposed solution employs three helper columns, which may be hidden for aesthetic purposes, on the 'percent' sheet populated by the following array entered formula*:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The blue table on the 'rank' sheet is then populated by the formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    There are ten more helper columns on the 'rank' sheet divided into two tables, yellow and green.
    The formula that populates the green table is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The formula that populates the yellow table is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The formula that populates the 'output' sheet is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Note: the 'output' sheet formula only populates the rows headed 'Insert' and 'let og' as I don't understand the criteria for populating the rows headed 'keep' (please provide me with more information).
    Zero values in the 'output' table are hidden using conditional formatting.
    *Array entered formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    Let us know if you have any questions.
    Thank you very much!

    However there is a few errors, in the output, if they can be fixed i will be able to use this!

    In the output for 15.01.2017 city 2 and city 6 is in the "let og" should be blank
    In the output for 30.01.2017 city 2 is there in "let og" should be blank and city 4 should be insert

    I am not able to correct this myeself in the code.

    And I have another question,
    If I would like to have top 10 instead of a top 3 rank insert and let go, how do i scale it? with the code?

  13. #13
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,208

    Re: Rank data and output data based on rank

    I need to know more about the criteria for 'let go' and 'insert'. I was under the impression that the top three cities by percentage, for that date, would be included in 'insert' and that any other cities that had a percentage for that date would be in 'let go', so that only cities without a percentage for that date would be left blank. Apparently that is incorrect.
    In order to have a top 10 the number of helper columns would need to be increased, however I feel that is secondary to establishing the rational behind including cities into the 'insert' and 'let go' categories.
    Let us know if you have any questions.

  14. #14
    Registered User
    Join Date
    07-16-2017
    Location
    norway
    MS-Off Ver
    365
    Posts
    46

    Re: Rank data and output data based on rank

    Quote Originally Posted by JeteMc View Post
    I need to know more about the criteria for 'let go' and 'insert'. I was under the impression that the top three cities by percentage, for that date, would be included in 'insert' and that any other cities that had a percentage for that date would be in 'let go', so that only cities without a percentage for that date would be left blank. Apparently that is incorrect.
    In order to have a top 10 the number of helper columns would need to be increased, however I feel that is secondary to establishing the rational behind including cities into the 'insert' and 'let go' categories.
    Let us know if you have any questions.
    Sorry. Let me try again.

    we rank top 3 out of 7 for any date.
    The value for the top 3 for the given date is extracted from data and inserted in the output tab for the current dates.

    Next date the data corresponding to the new top 3 will be inserted in the output. Some of the top 3 ranked cities might have been top 3 still, than the new value is inserted.
    The cities that might no long be top 3 will be taken out, so the value from the data needs to be inserted.

    In this example it will be like (values corresponding with the cities need to be put in output tab):
    01.01.2017:
    Insert: city: 7,1,3
    15.01.2017:
    let go: 7, 1
    Keept: 3
    Insert: 5,3,4

    30.01.2017
    let go: 3, 4
    Keept : 5
    Insert: 7, 1, 5

  15. #15
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,208

    Re: Rank data and output data based on rank

    See if this works better:
    The formula that populates the 'output' sheet is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The formula that populates the 'insert' table' on the 'rank' sheet is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The formula that populates the 'let go' table' on the 'rank' sheet is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The formula that populates the 'keep' table' on the 'rank' sheet is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Please do not quote entire posts.
    Let us know if you have any questions.
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    07-16-2017
    Location
    norway
    MS-Off Ver
    365
    Posts
    46

    Re: Rank data and output data based on rank

    Thanks very much JetMc.
    This seems to work.

    I however meet another problem when trying to scale it up..
    Error message:
    "Excel ran out of resources while attempting to calculate one or more formulas. These formulas can therefore not be evaluated."

    Its on a Excel for MAC. I will try on another windows machine later.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 4
    Last Post: 07-13-2017, 05:45 PM
  2. Replies: 3
    Last Post: 02-08-2016, 06:55 PM
  3. Replies: 2
    Last Post: 04-27-2015, 08:50 AM
  4. I need a formula that will rank based on highest data
    By agorman83 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-13-2014, 08:50 AM
  5. [SOLVED] Rank Data based on Criteria
    By vicor0509 in forum Excel General
    Replies: 3
    Last Post: 04-27-2012, 10:24 AM
  6. top data based on rank
    By jw01 in forum Excel General
    Replies: 8
    Last Post: 11-24-2011, 12:52 PM
  7. Replies: 1
    Last Post: 08-15-2005, 05:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1