+ Reply to Thread
Results 1 to 17 of 17

How to auto sort a spreadsheet

  1. #1
    Registered User
    Join Date
    05-30-2013
    Location
    Elberta, Al
    MS-Off Ver
    Excel 2007
    Posts
    91

    How to auto sort a spreadsheet

    I'm trying to learn how to do auto sort. I Know how to do the custom manual sort but it is taking to long to do my spreadsheet every night. I love to play the greyhounds but to manually sort 18 columns a race with 15 races each night takes a while there has to be a easier way. Thanks for any help


    here a sample of what I'm trying to do. The columns are in two C-D E-F G-H I-J and so on. The rows here are 7-14 as you see I have to sort some high and some low. Is this possible to do a auto sort for each set of columns

    C---- D----------E------F--------G------H--------I------J
    BEST-------------AVG-------------EARLY-----------LATE
    SPEED-----------SPEED------------SPEED---------- SPEED
    BOX--LOW-------BOX--LOW------BOX----HIGH------BOX--HIGH
    1---- 31.3-------1---- 31.47 ----- 1-----87.55------1-------0
    2---- 31.2-------2-----31.69------2---- 51.53------2-----(-1
    3---- 31.85------ 3-----31.95------3-------0--------3-------0
    4---- 31.36------4-----31.83------4-----45.53------4-----(-2
    5---- 31.4-------5-----31.91 ------5-----17.5-------5-------6
    6---- 31.4-------6-----31.84 ------6-----38.52------6-------1
    7---- 31.28------7-----31.47 ------7-----100--------7-------1
    8---- 31.03 ------8-----31.46------8----94.55-------8-----(-1
    Last edited by justme1052; 07-25-2013 at 06:36 PM. Reason: numbers were running together,maybe this will work

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,556

    Re: Not so simple to me , to auto sort a spreadsheet

    Hi and welcome to the forum

    If the data is always the same (format/volume-wise), and the process you go through each time is exactly the same, try recording a macro to do it for you (its easier than you might think).

    1st, make sure you SAVE your file - and maybe even make a copy so you wont lose anything.
    2nd, practice what you want to do a few times, so you know exactly what you are doing.
    3rd, click the macro record button - bottom left, next to the READY icon.
    4th, go through the steps as you would normally
    5th when finished, click the record button again

    your macro is now ready. you can activate it either through a button (you will need to create/add this and allocate the macro to it) or you can select VIEW on the ribbon, MACRO/view and click on the macro.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    05-30-2013
    Location
    Elberta, Al
    MS-Off Ver
    Excel 2007
    Posts
    91

    Re: Not so simple to me , to auto sort a spreadsheet

    Thanks for your reply. I guess I just do not understand macros, I did as you said starting saving the file in a different name. I click on the macro record button and did as I always do, start sorting the 15 races, after I finished I clicked off the record button. I saved the file and then downloaded today races. I open the file and with the record macro button off, I clear last night race from the worksheet. I copied and pasted the new race
    data to the worksheet and then I clicked on the micro record button. nothing happen, no sorting at all.
    So I open the original file ( glad I save it) and again saved it to a different name. I clicked on the micro record button and I again sorted the the 18 columns, set of 2 columns at a time with the high, low depending on the column. when finished I clicked off the record button. I closed out the file and reopened it to add tonight races. this time instead of clearing off the old data, I clicked on the record button and paste over the old data. but still it did not sort the new data. what if I may asked what am I doing wrong. I did not try the VBA code nor do I have a code or know where to get one. as you can tell I'm totally lost on this macros subject. LOL YOU SAID THIS WAS EASY. Well maybe for you, but I'm still scratching my head on this one.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,556

    Re: Not so simple to me , to auto sort a spreadsheet

    hmm ok so is your data in exactly the same format (columns, rows etc)?

  5. #5
    Registered User
    Join Date
    05-30-2013
    Location
    Elberta, Al
    MS-Off Ver
    Excel 2007
    Posts
    91

    Re: Not so simple to me , to auto sort a spreadsheet

    yes everything is the same as in the example in my first post, the only things that change is the new daily data.
    I turn on the macros recording button and manually sort the 15 race first, turn off the recording button when finished and save the file. do I save the file before I turn off the recording button? thanks again

  6. #6
    Registered User
    Join Date
    05-30-2013
    Location
    Elberta, Al
    MS-Off Ver
    Excel 2007
    Posts
    91

    Re: How to auto sort a spreadsheet

    yes everything is the same as in the example in my first post, the only things that change is the new daily data.
    I turn on the macros recording button and manually sort the 15 race first, turn off the recording button when finished and save the file. do I save the file before I turn off the recording button? thanks again

  7. #7
    Registered User
    Join Date
    07-19-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    92

    Re: How to auto sort a spreadsheet

    THis has me troubled:

    pasted the new race data to the worksheet and then I clicked on the micro record button. nothing happen, no sorting at all.
    Sounds like you clicked the macro record button when you wanted, instead, to RUN a macro you already recorded.

    FDibbins said
    your macro is now ready. you can activate it either through a button (you will need to create/add this and allocate the macro to it) or you can select VIEW on the ribbon, MACRO/view and click on the macro.
    Try that.

  8. #8
    Registered User
    Join Date
    05-30-2013
    Location
    Elberta, Al
    MS-Off Ver
    Excel 2007
    Posts
    91

    Re: How to auto sort a spreadsheet

    you kinda lost me.I thought once I recorded a macros and did the manual custom sort showing what I was wanting it would , when I wanted to add new data all I had to do was turn the macro button back on in the worksheet I made the recording on, add the new data and it would do the sorting. when it had finished I would
    turn off the recording, print my program and start again tomorrow with new data. but I'm not doing something right. FDibbins instructions seemed easy enough and I liked that part but I'm overlooking a very important step
    some where and I can not figure out where. you guys are both great for helping and I do thank you.

  9. #9
    Registered User
    Join Date
    07-19-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    92

    Re: How to auto sort a spreadsheet

    A macro is a list of keypresses. You "record" one, then you use it (and reuse it and reuse it). That's a macro's purpose.

    FDibbins gave you the steps to create a macro

    1st, make sure you SAVE your file - and maybe even make a copy so you wont lose anything.
    2nd, practice what you want to do a few times, so you know exactly what you are doing.
    3rd, click the macro record button - bottom left, next to the READY icon.
    4th, go through the steps as you would normally
    5th when finished, click the record button again
    your macro is now ready
    Then he described how to "use" that now-recorded macro.

    you can activate it either through a button (you will need to create/add this and allocate the macro to it) or you can select VIEW on the ribbon, MACRO/view and click on the macro.

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,556

    Re: How to auto sort a spreadsheet

    aaahhh ok yes, I think you are trying to start recording a new macro. That "record" button is a simple ON/OFF switch - turn it on to record/turn it off to stop recording. It does not run/activate the macro, I think you missed the last part of my instructions

    To run the macro, select View on the ribbon, click MACRO/view and click on the macro.

  11. #11
    Registered User
    Join Date
    05-30-2013
    Location
    Elberta, Al
    MS-Off Ver
    Excel 2007
    Posts
    91

    Re: How to auto sort a spreadsheet

    I promise that's what I thought I did and like I said that seemed easy enough to understand, but it did not work.
    I can go to the view and see my original worksheet or hit the shortcut key I created for that worksheet and it will open it up. but when I add new data same format just like the original, it does not sort. does copying and paste the new data wipe out the original worksheet that I put manual custom sort in. do I copy and paste the new data first then turn on the recording macros button an it will read and sort it then or did it not recognize the manual sort when i put it in. when I first did the manual sort i had of course has the data in the worksheet, and it sorted each column like it always has. when I finished all 15 races, I turn off the recording button and then save the file. I closed it out then reopen it and turn on the record button and add new data, but it did not sort. what did I forget or do wrong.

  12. #12
    Registered User
    Join Date
    07-19-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    92

    Re: How to auto sort a spreadsheet

    I closed it out then reopen it and turn on the record button and add new data
    This still tells me you are reusing the record mechanism.

    You only need to record it once.

    Then after that you "use" it.

  13. #13
    Registered User
    Join Date
    05-30-2013
    Location
    Elberta, Al
    MS-Off Ver
    Excel 2007
    Posts
    91

    Re: How to auto sort a spreadsheet

    wait!! I know I'm a old man (65) and trying to learn something new seemed to take a while for me, but I think between you and FDibbins not giving up it may have just clicked for me. are you saying once I make a recording, then I never have to turn on the recording macros again, just open the worksheet ,copy and paste the new data to the worksheet with the recording button off. since I made a recording. I just add the new data it will sort itself automatically. the only time I need to turn on the recording button again is if I create a new worksheet for another project. please tell me I am understanding now what you both were trying to tell me. if so, I will gladly close out the original worksheet and create another one. I kinda feel dumb now if I'm just now catching on to what you guys said and I hope now to make it work. just one more question. after I created a new worksheet and do the recording, when I'm finished and turn off the recording can I clear the contents hit save, then tomorrow add the new data without turning macros on and my recording will remain ok and do its sorting. I fell pretty good right now please don't come back and say not so fast. I think and hope I finally understand what your asking me to do.

  14. #14
    Registered User
    Join Date
    07-19-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    92

    Re: How to auto sort a spreadsheet

    Not quite - but closer. ;-)

    are you saying once I make a recording, then I never have to turn on the recording macros again, just open the worksheet ,copy and paste the new data to the worksheet with the recording button off.
    Yes.

    I just add the new data it will sort itself automatically.
    Not quite. You "use" it (make it run) by clicking its name in your macro list. You can click it over and over and it performs all the steps it recorded from your keystrokes originally.
    So you need to add the new data, then find the macro and run it per FDibbins instructions earlier

  15. #15
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,556

    Re: How to auto sort a spreadsheet

    I know all about slow learning, Im 55, so I salute your willingness to try new things

    there are 2 stages/processes to what we are going through with you.

    Step 1 is to create the set of commands that will be carried out when you tell it to. That part is what we call "recording the macro".
    This is kind of like when you recored a song on a cassette tape - you pressed "record", played the music, and when the song was finished, you pressed "record" again to stop recording

    Step 2 is to actually use the recording you just made. As with listening to music, this step has nothing to the "recording" step. So you now need to forget what you just did to record, and then follow my instructions on how to activate/use/listen to the recording you made.
    To run the macro, select View on the ribbon, click MACRO/view and click on the macro.

  16. #16
    Registered User
    Join Date
    05-30-2013
    Location
    Elberta, Al
    MS-Off Ver
    Excel 2007
    Posts
    91

    Re: How to auto sort a spreadsheet

    FDibbins, hey your back. I want to thanks you and Mike again. yes I finally got it. from start to finished. you were right once you understand it it is pretty easy. I do not totally understand all about Macros but thank to you and Mike I can now do my spreadsheet a whole lot faster. I created a new worksheet and did all the columns for one race just a little while ago and did step by step after finally understanding what you were telling me. guess what AUTO SORT really does work. thanks again. I owe you both big time.

  17. #17
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,556

    Re: How to auto sort a spreadsheet

    Im just happy you got your question resolved, and Im sure I speak for Mike too

    I will mark this thread SOLVED for you (this time ), please see my notes below on how to mark a thread SOLVED for future reference

+ 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. Auto sort golf tournament spreadsheet
    By timparker216 in forum Excel General
    Replies: 12
    Last Post: 09-25-2017, 02:26 AM
  2. Need help- Auto Sort data and auto subtotal
    By lesliewheeler in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-28-2013, 07:11 AM
  3. Help with Rank, Auto Sort Features (Spreadsheet Attached)
    By jagerman18 in forum Excel General
    Replies: 2
    Last Post: 08-22-2012, 06:46 PM
  4. Auto Sort, Auto Lock, Auto Date & Time Stamp
    By suehatesyou in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-23-2010, 02:57 PM
  5. [SOLVED] how do I set up a excel spreadsheet to auto sort data in order
    By jason in forum Excel General
    Replies: 0
    Last Post: 01-03-2006, 03:20 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