1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sort and Select Based on Time

Discussion in 'Ask an Excel Question' started by Adriel R., Oct 14, 2017.

  1. Adriel R.

    Adriel R. New Member

    Messages:
    15
    Have a 51,015 row data set from a datalogger. Each entry is date and time stamped (used find and replace to separate into two columns). My eQUEST professor knows 2003 as do I and we are clueless on 2007 (I have a couple Dummy books coming). He said there was a way to specify rows to hide in order to go from data every five seconds to every minute, but couldn't help beyond that. Anyone know how to do this?

    Once the data I am seeking is the only visible, then will select visible, copy, and paste into a new sheet.

    Thank y'all in advance! :D
  2. p45cal

    p45cal Well-Known Member

    Messages:
    923
    Yes, attach those dummy books and there's sure to be a way..
  3. Adriel R.

    Adriel R. New Member

    Messages:
    15
    Great, so how?

    The books are from Amazon and don't come until Sunday. Trying to get this done today so can get the Applied Project presentation done.
  4. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,924
    Hi ,

    Suppose your data is in columns A , B and C , starting from row 2.

    Use column D as a helper column , and in D2 enter a formula :

    =IF(MOD(ROW(A1)-1,12) = 0,ROW(A1), "")

    Copy this down as far as your data extends.

    Now , click on Filter to insert the AutoFilter arrows in columns A through D.

    In column D autofilter , uncheck the checkbox labelled (Blanks).

    You should now have rows visible which have data for every minute instead of for every 5 seconds.

    Copy and paste the visible rows to another worksheet.

    Narayan
  5. p45cal

    p45cal Well-Known Member

    Messages:
    923
    I thought you meant dummy (as in sample) books (as in Excel workbooks).
    See attached for one of many ways. There's a formula in column B (=MINUTE(A3)<>MINUTE(A2))and the list has been filtered. There's no need to split the timestamp unless it's not recognised as a proper date/time by Excel.

    Attaching a workbook yourself here would stop us guessing (wrongly) just what's in your sheet, perhaps even the raw log file.

    Attached Files:

  6. Adriel R.

    Adriel R. New Member

    Messages:
    15
    Narayan, thank you!

    I didn't attach in fear someone just do it for me, like the professor does and then don't know how.

    Date Time 101 <Outside Brick Surface T> (F) 102 <Inside Brick Surface T> (F) 104 <Drywall Surface T> (F) 105 <Indoor Air T> (F) 106 <Outdoor Air T> (F)

    This is A though G, in order.

    What is a helper column?

    What are "AutoFilter arrows? Where are these?
  7. Adriel R.

    Adriel R. New Member

    Messages:
    15
    Apparently the attach file feature is not available.

    I choose the file and then nothing happens.
  8. p45cal

    p45cal Well-Known Member

    Messages:
    923
    How big is the file?
  9. Adriel R.

    Adriel R. New Member

    Messages:
    15
    Looking at that, the seconds are still not sorted. Then some strange buttons at the top of the columns.

    I split it because you can not graph data that has a date and a time, the graph was blank.

    I sure miss the good old days of 2003. Seems a lot of the features were removed in 2007 and that loathsome useless ribbon.

    36,522KB.

    Good point, maybe since eight sheets (all different tries) move to a new workbook.
  10. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,924
    Hi ,

    See if this helps.

    Narayan

    Attached Files:

  11. Adriel R.

    Adriel R. New Member

    Messages:
    15
    Trying again to attach a file. This time waited for the bar to quit dancing. Sure wish you could see if attached before sending.

    Damn I hate this platform! Why the hell will it not attach?
  12. p45cal

    p45cal Well-Known Member

    Messages:
    923
    I don't know what you mean by this. A workbook of some sort from you should make this clearer.
    You could use an external file sharing site such as dropbox, google drive, box.net…
    and give the link here
  13. Adriel R.

    Adriel R. New Member

    Messages:
    15
    Sure does and sure appreciated, as see where need to head. Now have to go play around and see if I can get there.
  14. Adriel R.

    Adriel R. New Member

    Messages:
    15
    On all the forums I have been on, it was to fiddling around, simply attach. Better yet, could see it attaching instead of this ludicrous floating box.

    Where is this Google Drive for this forum?
  15. Adriel R.

    Adriel R. New Member

    Messages:
    15
  16. p45cal

    p45cal Well-Known Member

    Messages:
    923
    Shortened file attached.
    Both sheets have had rows hidden (filtered) and a helper column added.

    Attached Files:

  17. Adriel R.

    Adriel R. New Member

    Messages:
    15
    Can you please explain?

    Not seeing how the true/false is created.
  18. p45cal

    p45cal Well-Known Member

    Messages:
    923
    By the formula in the same cells!

    The likes of:
    =MINUTE(A3)<>MINUTE(A2)
    is the same as:
    =IF(MINUTE(A3)<>MINUTE(A2),TRUE,FALSE)
  19. Adriel R.

    Adriel R. New Member

    Messages:
    15
    Why yell at me? Fantastic having "=MINUTE(B3)<>MINUTE(B2)" but what is it doing and how?

    See, this is the problem as I stated earlier, of simply and going in and doing. Different strokes for different folks; myself am a tactile learner.
  20. p45cal

    p45cal Well-Known Member

    Messages:
    923
    In Excel, go into edit one of the formulae and put the edit cursor somewhere on the word MINUTE,
    upload_2017-10-14_20-12-14.png
    then at the left of the formula bar click the fx button, you'll get:
    upload_2017-10-14_20-14-11.png
    then if you click on Help on this function you should get more detailed information on the function.
    To see the function operating by itself, just use it by itself in a cell:
    upload_2017-10-14_20-24-16.png
    See how the value changes as it flips from one minute to the next.
    What's more, in column B above, you can see the TRUE result when two adjacent timestamps do not have the same MINUTE.

    No one's yelling.
    r2c2 and Thomas Kuriakose like this.
  21. Adriel R.

    Adriel R. New Member

    Messages:
    15
    An exclamation point was taught in English class as yelling... Meh, not pertinent to discuss.

    Odd thing was just finished retyping the formula and applying it when got the notification of your reply. The only one that was a problem was the first, so had it compare to itself making it FALSE like it should be.

    Now how do I set the filter? Edit: I am a dummy... Forgot to inform what to filter...

    Again, thank you.

    Attached Files:

  22. p45cal

    p45cal Well-Known Member

    Messages:
    923
    I got around that one by leaving the formula out altogether for that cell, and when filtering including both TRUE cells and (blanks).

    If selecting a single cell in the table then clicking the Filter button doesn't work, then select the block of cells you want to filter in its entirety, including the header row before clicking the filter button.
  23. Adriel R.

    Adriel R. New Member

    Messages:
    15
    If leave a blank, Excel generates that option. Man, my head hearts from all this learning and discovering. ;p

    Now finding it is not at the :00 but :55. See how good to talk out, be because of the starter. Edit: yes, basically it. Now thinking should have done five minutes... LOL

    Am so grateful to have this done rather than waiting until Monday.
    Last edited: Oct 14, 2017
  24. Adriel R.

    Adriel R. New Member

    Messages:
    15
    Thank y'all for the help, feels amazing to be done so early. :D

    Attached Files:

  25. p45cal

    p45cal Well-Known Member

    Messages:
    923
    What did you do to lose the errant data (inside brick and drywall surfaces)?:
    upload_2017-10-15_10-19-11.png

Share This Page