• 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...

  • 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

Adriel R.

New Member
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
 
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
 
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.
 

Attachments

  • Chandoo36095.xlsx
    10.1 KB · Views: 3
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

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?
 
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.

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.

How big is the file?

36,522KB.

Good point, maybe since eight sheets (all different tries) move to a new workbook.
 
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?
 
Looking at that, the seconds are still not sorted.
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
 
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

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?
 
Shortened file attached.
Both sheets have had rows hidden (filtered) and a helper column added.
 

Attachments

  • Chandoo36095KoinoniaData3.xlsb
    871.4 KB · Views: 3
By the formula in the same cells!

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.
 
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.
 
In Excel, go into edit one of the formulae and put the edit cursor somewhere on the word MINUTE,
View attachment 46453
then at the left of the formula bar click the fx button, you'll get:
View attachment 46454
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:
View attachment 46456
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.

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.
 

Attachments

  • Filter.JPG
    Filter.JPG
    349.7 KB · Views: 2
The only one that was a problem was the first, so had it compare to itself making it FALSE like it should be.
I got around that one by leaving the formula out altogether for that cell, and when filtering including both TRUE cells and (blanks).

Now how do I set the filter? Edit: I am a dummy... Forgot to inform what to filter...
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.
 
I got around that one by leaving the formula out altogether for that cell, and when filtering including both TRUE cells and (blanks).

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:
Back
Top