Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sort by date/time distributing to separate sheets (97)

    I have some data on one sheet that I need to redistribute to new sheets within the same workbook.

    The criteria for sorting is time and date, but our days run from 6:PM to 6:PM the following day.

    The date and time are in one cell in this format: 12/6/2002 11:52:11 PM and 12/7/2002 1:23:11 AM

    Here's what I need done:
    Create and name new worksheets (I know how to do this)
    Distribute the data to the new worksheets based on the time and date. For example, the dates above would be on the worksheet named "Friday". Anthing that falls within the next 6:PM to 6:PM "slot" would be on the sheet "Saturday".
    And this should be repeated for each day.

    I appreciate all the help I receive here.

    Thanks in advance!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sort by date/time distributing to separate sheets (97)

    How far do you want to automate this? The following might get you started; post back if you want more help.

    You can add an extra column (adjacent to the current data) to sort/group the way you want. Say your dates/times are in A2:A500 (with A1 as column header), and the auxiliary column is column D.
    In cell D1, enter a new column header, for instance SortDate.
    In cell D2, enter the formula =INT(A2-0.25), and format D2 to display just the date.
    Fill down to D500 (you can probably double click the fill grip in the lower right corner of D2 to do this).
    Sort the entire table on SortDate.

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort by date/time distributing to separate sheets (97)

    How often do you need to do this? If just once, you can use the technique Hans gave you. If you have to do this frequently, it will take a macro to completely automate. If you want help with a macro, we will need to know more about the worksheet, and exactly what you want to do. Which column are the dates in? How many columns need to be copied? Do the sheets that are to be copied to already exist or should the macro create them? If the macro should create them, what should it do if the sheet already exists? If they already exists, what should the macro do if the sheet already contains data? Are there header rows on the original sheet, and if so do they need to be copied to the new sheets if the macro creates them?

    Posting an example file might get you an answer faster.
    Legare Coleman

  4. #4
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort by date/time distributing to separate sheets (97)

    I've attached an example of what I need done.

    The sheet "FLAT" contains the raw data. The sheets named after days of the week contain the sorted data.

    For full automation, only the sheet "FLAT" would exist initially. I can insert new worksheets and names, so I don't really need help with that.

    Also, this is done once a week, usually on Fridays.

    If more info is needed, let me know.

    Again, I really appreciate all the help I get.

    (Edit to fix sheet name)
    Attached Files Attached Files

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort by date/time distributing to separate sheets (97)

    The attached workbook contains a macro named SortRows that I think does what you want. I was a little unclear because the workbook you uploaded contained worksheets named Friday, Saturday, Sunday, and Monday-Friday. I wasn't sure if the Monday-Friday meant 5 worksheets or one (the macro is for five). I particularly was not sure what to do about Friday.
    Attached Files Attached Files
    Legare Coleman

Posting Permissions

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