+ Reply to Thread
Results 1 to 32 of 32

Auto sort values from highest to lowest based off of value

  1. #1
    Registered User
    Join Date
    07-02-2008
    Location
    Portland, Or.
    Posts
    13

    Auto sort values from highest to lowest based off of value

    I need the close% column to auto sort from highest to lowest so that I can see at a glance who the top sales person is. I have conditional formatting for the top three but I would rather them auto sort by close%, can anybody help me with this. I have attached the file, thanks for your help!
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    06-17-2008
    Posts
    50
    Select each block of cells one at time..

    So for the first one, highlight cells A6:D17. Under the home ----> editing section, select sort and filter ----> custom sort. For sort by selection close% and then change order to largest to smallest. You will have to do that same thing for each block of cells, but that should do it.

  3. #3
    Registered User
    Join Date
    07-02-2008
    Location
    Portland, Or.
    Posts
    13
    Thank you for the response but that's not exactly what I need done. I need it to auto sort whenever the values are changed so that I don't have to do it manually each time I put in numbers.

  4. #4
    Registered User
    Join Date
    07-02-2008
    Location
    Portland, Or.
    Posts
    13
    I hope I explained my issue well enough, does anybody know how to do this?
    Last edited by qj67; 07-08-2008 at 01:03 PM.

  5. #5
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    Sort in ascending Order

    See the attached example.
    On Sheet1, the marked column of data gets sorted in ascending order every time you add another value in that range or delete or change a value.
    Hope it helps.
    Expand the range as you need in the Macro.
    modytrane
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    Sort In Ascending Order

    Here's your file.
    I had to create column A with copies of pct and sort all five column.
    If you change any value for any sales rep.i Nashua, that block will automatically sort.
    Hope it helps.
    modytrane.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    07-02-2008
    Location
    Portland, Or.
    Posts
    13
    That is exactly what I want it to do thank you! But how do I do it for the other 3 blocks of info?

  8. #8
    Registered User
    Join Date
    07-02-2008
    Location
    Winnipeg, MB
    Posts
    57
    Quote Originally Posted by qj67
    That is exactly what I want it to do thank you! But how do I do it for the other 3 blocks of info?
    This is exactly what I'm looking for. How can I apply this to my worksheet? I don't see how it has been done! LOL

  9. #9
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    Sort Automatically Based on %

    Take a look at the attached file. This does what you wanted.
    Have Fun,
    modytrane
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    07-02-2008
    Location
    Portland, Or.
    Posts
    13
    Thank you Modytrane, I really appreciate you!

  11. #11
    Registered User
    Join Date
    05-15-2010
    Location
    Oman
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Sort Automatically Based on %

    Quote Originally Posted by modytrane View Post
    Take a look at the attached file. This does what you wanted.
    Have Fun,
    modytrane
    modytrane - how did you do it - it triyed to do the same with my excel sheet but i couldn't figure it out how did you do it. could you please explain.

    cheers
    Hamadah

  12. #12
    Registered User
    Join Date
    05-15-2010
    Location
    Oman
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Auto sort values from highest to lowest based off of value

    any one can help me on that please?

  13. #13
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    Re: Auto sort values from highest to lowest based off of value

    Here's the Macro used in the example posted before.

    Please Login or Register  to view this content.

    Paste this in to your Worksheet_Change _Event.

    Hamadah,
    I don't know your application.
    If you can't make it work, post a sample of your worksheet.

    modytrane

  14. #14
    Registered User
    Join Date
    05-15-2010
    Location
    Oman
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Auto sort values from highest to lowest based off of value

    Quote Originally Posted by modytrane View Post
    Here's the Macro used in the example posted before.

    Please Login or Register  to view this content.

    Paste this in to your Worksheet_Change _Event.

    Hamadah,
    I don't know your application.
    If you can't make it work, post a sample of your worksheet.

    modytrane

    modytrane,
    thank you for helping me, i tried to make it work but i couldn't as my values are getting the data from two tables and when i use the code everything mixed up. have a look in attached file.
    what i try to sort is the NPT % - first NPT table i couldn't make it happened as values connected to formulas but when i made a copy just below it taking formulas out the code had worke. so what i need to sort is the first table as i need the data sorted Automaticly when ever there is a change on related data.
    Attached Files Attached Files
    Last edited by Hamadah; 05-22-2010 at 06:59 AM.

  15. #15
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    Re: Auto sort values from highest to lowest based off of value

    try the attached file and let me know if this works for you.


    If you change anything on any of the three sheets, it will sort the NPT % table on "Contractors" sheet.

    modytrane
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    05-15-2010
    Location
    Oman
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Auto sort values from highest to lowest based off of value

    Quote Originally Posted by modytrane View Post
    try the attached file and let me know if this works for you.


    If you change anything on any of the three sheets, it will sort the NPT % table on "Contractors" sheet.

    modytrane
    modytrane
    Yes it is working very fine.Thank you thank you thank you so much. you are genius. i spent more than three weeks on it but i couldn't do it. I am realy appreciated. thank you again.

    best regards,
    hamadah

  17. #17
    Registered User
    Join Date
    05-15-2010
    Location
    Oman
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Auto sort values from highest to lowest based off of value

    modytrane

    Unfortunately it didn't work with the "Data worksheet". When i tested on "Contractors worksheet" it was working fine but all the data source came from "Data Worksheet".please have a look in attached file i added the last row with yellow colour but it didn't work. at the same time the NPT average column on "Contractors" worksheet lost one of the green colour.

    It is work when i go to the "Contractors worksheet" and click on an empty cell then press Delete Function at the keyboard then we will be able to see it working. So if i protect the "Contractors worksheet" by password, it will be also looks like manual sorting because i have to unprotect the "Contractors worksheet" first then double click on an empty cell or press Delete keyboar function so i can see the effect of changes.
    Attached Files Attached Files
    Last edited by Hamadah; 05-24-2010 at 02:58 AM.

  18. #18
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    Re: Auto sort values from highest to lowest based off of value

    Take a look at the attached file.
    When I change values on "Data" sheet, it sorts the table on "Contractors" sheet.
    I added a line in the macro that increses cell "B92" every time the sorting macro runs. It's just for testing. You can rmove that line later.

    You didn't loose one green color. Look at your conditional formatting formula. You are looking for five highest values from "K73:O88". And K73 is 97.2%. so you're only coloring next four highest values in column O.

    If you "Contractors" sheet is normally protected, you may have to unprotect it in the macro, before sorting and then protect it before leaving the mcaro.

    If you need help with that, let me know.
    modytrane.
    Attached Files Attached Files

  19. #19
    Registered User
    Join Date
    05-15-2010
    Location
    Oman
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Auto sort values from highest to lowest based off of value

    modytrane
    Sorry for late i was in vacation, Thank you so much, it is working and every thing is working perfectly. i am so much appreciated. thank you again,

    is there any way to protect "contractors" sheet without effecting the autosorting, so no body can toutch it or miss with it?, because when i tried to protect it and add some value into the "Data" sheet a window showed up said that there is a debug in the system.

  20. #20
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    Re: Auto sort values from highest to lowest based off of value

    In the attached file, following code is used. It resides in module 1.
    I've used "xyz" as the password. Change it to your password and the sheet will be unprotected before doing th sort and macro will protect it again after the sort.

    Please Login or Register  to view this content.
    modytrane
    Attached Files Attached Files

  21. #21
    Registered User
    Join Date
    05-15-2010
    Location
    Oman
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Auto sort values from highest to lowest based off of value

    modytrane,

    It is amazing, thank you so much for your help, i learned alot from you. The worksheet looks excellent now because of you. thank you again and i am so much appreciated.

  22. #22
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    Re: Auto sort values from highest to lowest based off of value

    If you are satisfied with the solution, please mark this thread "SOLVED".
    modytrane

  23. #23
    Registered User
    Join Date
    05-15-2010
    Location
    Oman
    MS-Off Ver
    Excel 2003
    Posts
    13

    Thumbs up Re: Auto sort values from highest to lowest based off of value

    Solved ---------------------------------------

  24. #24
    Registered User
    Join Date
    05-15-2010
    Location
    Oman
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Auto sort values from highest to lowest based off of value

    Quote Originally Posted by modytrane View Post
    If you are satisfied with the solution, please mark this thread "SOLVED".
    modytrane

    modytrane
    Sorry if i come back to this problem again but I did install Windows 7, Unfortunately the excel file is not working as before. i don't know why.

    Please have a look in the attached file.
    Attached Files Attached Files

  25. #25
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    Re: Auto sort values from highest to lowest based off of value

    I checked the file and it looks like its doing the sortinng when I change one of the values in the table.
    I checked it for a few different cells.
    Please tell me in detail what part is not working.
    Specifically tell me what values you are changing, what you are expecting and how the program is reacting differently than your expectation.
    Without specific example, its hard to troubleshoot the problem.
    modytrane.

  26. #26
    Registered User
    Join Date
    05-15-2010
    Location
    Oman
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Auto sort values from highest to lowest based off of value

    modytrane,

    As you know the file was working very fine before i installed Windows 7. please have a look in attached file. i explain everything in there.
    Attached Files Attached Files

  27. #27
    Registered User
    Join Date
    05-15-2010
    Location
    Oman
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Auto sort values from highest to lowest based off of value

    modytrane, are you still following the issue with me?

  28. #28
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,979

    Re: Auto sort values from highest to lowest based off of value

    Hamadah, modytrane
    Why did you resurrect a two year old thread???? You are supposed to start your own. Ancient threads, reposted, should not be answered.

    Rule #2:
    2. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.
    Last edited by protonLeah; 07-11-2010 at 04:54 PM.
    Ben Van Johnson

  29. #29
    Registered User
    Join Date
    05-15-2010
    Location
    Oman
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Auto sort values from highest to lowest based off of value

    Quote Originally Posted by protonLeah View Post
    Hamadah, modytrane
    Why did you resurrect a two year old thread???? You are supposed to start your own. Ancient threads, reposted, should not be answered.

    Rule #2:
    2. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.
    It was my fault, sorry i am new here and i don't know the rules... well, i will make the new thread now.

  30. #30
    Registered User
    Join Date
    10-14-2010
    Location
    Baltimore, MD
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Auto sort values from highest to lowest based off of value

    I used this code for an automatic sort, but the sort only work if I manually go into the cells that changed and hit enter. Is there anything I can add to the code to make it sort without doing this manually? The data being sorted in range B54 comes from another sheet and goes into the sorted area via formulas.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Range("A54:E63").Select
    Selection.Sort Key1:=Range("B54"), Order1:=xlDescending, Header:=xlNo, _
    OrderCustom:=6, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
    Application.Calculation = xlCalculationAutomatic

    End Sub

    Thanks for any help!

  31. #31
    Registered User
    Join Date
    10-14-2010
    Location
    Baltimore, MD
    MS-Off Ver
    Excel 2010
    Posts
    2

    Question Re: Auto sort values from highest to lowest based off of value

    I used this code for an automatic sort, but the sort only work if I manually go into the cells that changed and hit enter. Is there anything I can add to the code to make it sort without doing this manually? The data being sorted in range B54 comes from another sheet and goes into the sorted area via formulas.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Range("A54:E63").Select
    Selection.Sort Key1:=Range("B54"), Order1:=xlDescending, Header:=xlNo, _
    OrderCustom:=6, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
    Application.Calculation = xlCalculationAutomatic

    End Sub

    Thanks for any help!

  32. #32
    Registered User
    Join Date
    06-24-2014
    Location
    Laurel,MD
    MS-Off Ver
    2010
    Posts
    1

    Re: Auto sort values from highest to lowest based off of value

    Hi
    I copied your macro and pasted into my sheet but i could not get it to work
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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