hide data for every 1minute

[only_lonely]

i got series of data which record the time in 10secs,i want to display the data in format for every 1 minute..how to do?

original data
11/25/2009 23:58:27:708
11/25/2009 23:58:37:708
11/25/2009 23:58:47:708
11/25/2009 23:58:57:708
11/25/2009 23:59:07:708
11/25/2009 23:59:17:708
11/25/2009 23:59:27:708
11/25/2009 23:59:37:708
11/25/2009 23:59:47:708
11/25/2009 23:59:57:708
11/26/2009 00:00:07:708
11/26/2009 00:00:17:708
11/26/2009 00:00:27:708
11/26/2009 00:00:37:708
11/26/2009 00:00:47:708
11/26/2009 00:00:57:708

[teylyn]

Hi, Only_lonely,

with readings every 10 seconds, you want to create a list of every 6th entry in the original list to return a reading for minute intervals.

To do that, if your source data is in Sheet1, column A, try

=INDEX(A:A,(ROW()*6)-6)

in B1 in Sheet1 and copy down. This will start collecting from the first data point and then collect every sixth occurrence in the row.

If your source data is in a different column, change the A:A bit to your column letter.

To create the appearance of "hiding" the other data points, put the formula in cell A1 in a new sheet and change it to


=INDEX(Sheet1!A:A,(ROW()*6)-6)


hth

[only_lonely]

now it becomes:

Time Original Time Changed
11/25/2009 13:15:47:724 11/25/2009 13:17:27:708
11/25/2009 13:15:57:708 11/25/2009 13:18:27:708
11/25/2009 13:16:07:842 11/25/2009 13:19:27:708
11/25/2009 13:16:17:708 11/25/2009 13:20:27:708
11/25/2009 13:16:27:708 11/25/2009 13:21:27:708
11/25/2009 13:16:37:708 11/25/2009 13:22:27:708
11/25/2009 13:16:47:708 11/25/2009 13:23:27:708
11/25/2009 13:16:57:708 11/25/2009 13:24:27:708
11/25/2009 13:17:07:708 11/25/2009 13:25:27:708
11/25/2009 13:17:17:708 11/25/2009 13:26:27:708
11/25/2009 13:17:27:708 11/25/2009 13:27:27:708
11/25/2009 13:17:37:708 11/25/2009 13:28:27:708
11/25/2009 13:17:47:708 11/25/2009 13:29:27:708
11/25/2009 13:17:57:708 11/25/2009 13:30:27:708
11/25/2009 13:18:07:708 11/25/2009 13:31:27:708

The output i want is to hide the unessary time
just display it at every 1minute instead of 10secs.
Time
11/25/2009 13:15:47:724
11/25/2009 13:16:47:708
11/25/2009 13:17:47:708
11/25/2009 13:18:47:708

[teylyn]

Hang on.

Is that all in one column or are we talking two columns?

Could you possibly post a small sample of your data in a spreadsheet? That would help immensely in finding a solution. A couple of dozen rows will suffice. Please give an example of "before" and "after" and explain the logic.

cheers

[only_lonely]

before, (behind B1 column,C1, D1 are all datas):

B1
Time
11/25/2009 13:15:47:724
11/25/2009 13:15:57:708
11/25/2009 13:16:07:842
11/25/2009 13:16:17:708
11/25/2009 13:16:27:708
11/25/2009 13:16:37:708
11/25/2009 13:16:47:708
11/25/2009 13:16:57:708
11/25/2009 13:17:07:708
11/25/2009 13:17:17:708
11/25/2009 13:17:27:708
11/25/2009 13:17:37:708
11/25/2009 13:17:47:708


after (data behind same row as B1 all deleted...example:row B2,C2,D2 deleted...) just show the data fro every 1 minute interval
B1
Time
11/25/2009 13:15:47:724
11/25/2009 13:16:47:708
11/25/2009 13:17:47:708

[teylyn]

could you possibly post a small sampe of your data in a spreadsheet?

..........

[only_lonely]

sure..i attached the excel sheet...
can i post pivot chart question here???

[teylyn]

CRIKEY! if even a zip pushes a MB, I'm out!

A couple of dozen rows would do nicely as a data sample to illustrate your issue. Overall file size would probably not exceed 20 kb, even with a couple of hundred rows.

[only_lonely]

before, (behind B1 column,C1, D1 are all datas):

B1
Time
11/25/2009 13:15:47:724
11/25/2009 13:15:57:708
11/25/2009 13:16:07:842
11/25/2009 13:16:17:708
11/25/2009 13:16:27:708
11/25/2009 13:16:37:708
11/25/2009 13:16:47:708
11/25/2009 13:16:57:708
11/25/2009 13:17:07:708
11/25/2009 13:17:17:708
11/25/2009 13:17:27:708
11/25/2009 13:17:37:708
11/25/2009 13:17:47:708


after (data behind same row as B1 all deleted...example:row B2,C2,D2 deleted...) just show the data fro every 1 minute interval
B1
Time
11/25/2009 13:15:47:724
11/25/2009 13:16:47:708
11/25/2009 13:17:47:708

[teylyn]

spreadsheet?

how long do you think we can keep this up?

[only_lonely]

spreadsheet too big...
before, (behind B1 column,C1, D1 are all datas):

columnB1
Time
11/25/2009 13:15:47:724
11/25/2009 13:15:57:708
11/25/2009 13:16:07:842
11/25/2009 13:16:17:708
11/25/2009 13:16:27:708
11/25/2009 13:16:37:708
11/25/2009 13:16:47:708
11/25/2009 13:16:57:708
11/25/2009 13:17:07:708
11/25/2009 13:17:17:708
11/25/2009 13:17:27:708
11/25/2009 13:17:37:708
11/25/2009 13:17:47:708


after (data behind same row as B1 all deleted...example:row B2,C2,D2 deleted...) just show the data fro every 1 minute interval
columnB1
Time
11/25/2009 13:15:47:724
11/25/2009 13:16:47:708
11/25/2009 13:17:47:708

[teylyn]

Ctrl-C -- Standard Toolbar - New -- Ctrl-V -- Ctrl-S

.... tuning out ....

If you make it so hard to help you ....

[only_lonely]

solved....by using auto fill in series n filter function....
nobody help me in this forum