software

Let us say there are three columns: A, B, and C.

Column A contains time formatted to [hh]:mm:ss and goes from 0:00:00 to
21:20:00. There are an arbitrary number of entries in this column.

Column B contains time formatted to [hh]:mm:ss and starts from 0:00:00
and ends at 21:30:00. -It increases in 15 minute intervals exactly.-

Column C contains data that is attached to Column A on the same row.

What I want is to make it that Column A's values match themselves to
Column B's values. I.E. For values of A between 00:00:00 and Column B
row 2 (00:15:00) I want the values of A (and therefore C) to line up
next to Column B row entry 00:00:00.

This isn't a very clear explanation. I will explain it again, and maybe
you can piece together what I mean.

There are many columns: Cumulative Time, 15 Minute Intervals and
several columns attached to the Cumulative Time column. The cumulative
time column is effectively a timestamp.

I want to sort my data so that the data values timestamped between,
say, 0 minutes and 15 minues will be together. The problem is, some 15
minute intervals have no entries, so I want a blank in the Cumulative
Time column next to the 15 minute interval which contains no time
points.

I will attach the spreadsheet. The columns of interest are B
(Cumulative Time) and C (15 minute intervals). All the other columns
should be associated with column A.

This will allow me to make 15 minute averages of my data (the columns N
to Q).

Thank you in advance. I really hope someone can help me, because this
problem has me really stumped. As you can see from the spreadsheet,
I've been tortuously grappling with this problem for a while!

Thanks,

Adam -------------------------------------------------------------------
|Filename: Test Rat 080206-090206 Complete.zip |
|Download: www.excelforum.com/attachment.php?postid=4592 |
-------------------------------------------------------------------

--
A S-D
------------------------------------------------------------------------
A S-D's Profile: www.excelforum.com/member.php...oamp;userid=33227
View this thread: www.excelforum.com/showthread...hreadid=530452
Hi there,

Looked at spreadsheet but cannot figure out what you want to do,
sorry.

You can make a lookup table so that any time in column B is assigned a
code number linked to a particular 15 minute slot.

The top of it would look like this

00:00:00 code1
00:15:00 code2
00:30:00 code3

say you have a time of 00:17:11 in cell A1 and the lookuptable is
called table1

=vlookup(A1,table1,2) returns code2

will wait for more info from you--
robert111
------------------------------------------------------------------------
robert111's Profile: www.excelforum.com/member.php...oamp;userid=31996
View this thread: www.excelforum.com/showthread...hreadid=530452
Thank you for your consideration, Robert111.

I don't know what a vlookup table is, I'm afraid.

Let me try to explain a little clearer, as I think your solution falls
into problems when an interval would have no entries.

I have data from a device. This device records the RApid Turn of some
apparatus, through a varying voltage. Each time the voltage changes
(i.e. the device detects a turn) an entry is made with the appropriate
timestamp from time zero which is the start of the recording.

So..

-5.00 Volts 02:56:41
0.00 Volts 02:56:44
5.00 Volts 02:57:03

I want to separate my data into 15 minute blocks, relative to the
start. How do I make it so that the timestamp maps itself to the 15
minute intervals? In some intervals, there was no activity at all, so
that interval should have no corresponding timestamps.

Is this even possible in Excel?--
A S-D
------------------------------------------------------------------------
A S-D's Profile: www.excelforum.com/member.php...oamp;userid=33227
View this thread: www.excelforum.com/showthread...hreadid=530452
Hi,

I cannot understand what you are trying to do with the 15 minute
period, if you are trying to asses the happenings of each 15 minute
period of items in column A, then the attached with a new column C
might help, but there are (15 minute) periods where there is no
activity asin between rows 4 and 5, and other 15 min periods where
there is much activity asin rows 5 to 18.

From the newly inserted column C, subtotals could be drawn for the 15
minute periods, and these could be the subject of a vlookup looking on
time to match up to a complete table of 15 minute increments if
required, however, your original 15 minute lots do not appear to be
'day' specific, should they be?

Hope this helps

--

A S-D Wrote:
gt; Let us say there are three columns: A, B, and C.
gt;
gt; Column A contains time formatted to [hh]:mm:ss and goes from 0:00:00 to
gt; 21:20:00. There are an arbitrary number of entries in this column.
gt;
gt; Column B contains time formatted to [hh]:mm:ss and starts from 0:00:00
gt; and ends at 21:30:00. -It increases in 15 minute intervals exactly.-
gt;
gt; Column C contains data that is attached to Column A on the same row.
gt;
gt; What I want is to make it that Column A's values match themselves to
gt; Column B's values. I.E. For values of A between 00:00:00 and Column B
gt; row 2 (00:15:00) I want the values of A (and therefore C) to line up
gt; next to Column B row entry 00:00:00.
gt;
gt; This isn't a very clear explanation. I will explain it again, and maybe
gt; you can piece together what I mean.
gt;
gt; There are many columns: Cumulative Time, 15 Minute Intervals and
gt; several columns attached to the Cumulative Time column. The cumulative
gt; time column is effectively a timestamp.
gt;
gt; I want to sort my data so that the data values timestamped between,
gt; say, 0 minutes and 15 minues will be together. The problem is, some 15
gt; minute intervals have no entries, so I want a blank in the Cumulative
gt; Time column next to the 15 minute interval which contains no time
gt; points.
gt;
gt; I will attach the spreadsheet. The columns of interest are B
gt; (Cumulative Time) and C (15 minute intervals). All the other columns
gt; should be associated with column A.
gt;
gt; This will allow me to make 15 minute averages of my data (the columns N
gt; to Q).
gt;
gt; Thank you in advance. I really hope someone can help me, because this
gt; problem has me really stumped. As you can see from the spreadsheet,
gt; I've been tortuously grappling with this problem for a while!
gt;
gt; Thanks,
gt;
gt; Adam -------------------------------------------------------------------
|Filename: Lab Rat 080206-090206 Complete.zip |
|Download: www.excelforum.com/attachment.php?postid=4593 |
-------------------------------------------------------------------

--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: www.excelforum.com/member.php...oamp;userid=21059
View this thread: www.excelforum.com/showthread...hreadid=530452Hi,
Given the following data from your spreadsheet , how do you want it
changed? Like Robert, I am struggling to see how you want it formatted.

Cumulative
Time15 minute
intervals Voltage of turn.
0:00:0000:00:00-0.06
0:18:4400:15:005.02
0:18:4500:30:00-0.02
1:16:5900:45:002.52
1:17:0001:00:00-0.04quot;A S-Dquot; wrote:

gt;
gt; Thank you for your consideration, Robert111.
gt;
gt; I don't know what a vlookup table is, I'm afraid.
gt;
gt; Let me try to explain a little clearer, as I think your solution falls
gt; into problems when an interval would have no entries.
gt;
gt; I have data from a device. This device records the RApid Turn of some
gt; apparatus, through a varying voltage. Each time the voltage changes
gt; (i.e. the device detects a turn) an entry is made with the appropriate
gt; timestamp from time zero which is the start of the recording.
gt;
gt; So..
gt;
gt; -5.00 Volts 02:56:41
gt; 0.00 Volts 02:56:44
gt; 5.00 Volts 02:57:03
gt;
gt; I want to separate my data into 15 minute blocks, relative to the
gt; start. How do I make it so that the timestamp maps itself to the 15
gt; minute intervals? In some intervals, there was no activity at all, so
gt; that interval should have no corresponding timestamps.
gt;
gt; Is this even possible in Excel?
gt;
gt;
gt; --
gt; A S-D
gt; ------------------------------------------------------------------------
gt; A S-D's Profile: www.excelforum.com/member.php...oamp;userid=33227
gt; View this thread: www.excelforum.com/showthread...hreadid=530452
gt;
gt;


Again, thanks for looking at this problem. I wish I could explain this
more simply!

Bryan, the column C is merely the whole testing period split into 15
minute intervals. It is what I want my data ordered by:

Cumulative
Time 15 minute
intervals Voltage of turn.
0:00:00 00:00:00 -0.06
0:18:44 00:15:00 5.02
0:18:45 00:30:00 -0.02
1:16:59 00:45:00 2.52
1:17:00 01:00:00 -0.04

Should become:
Code:
--------------------

15 Minute Intervals // Cumulative Time // Voltage of Turn (rest of data)

00:00:00 [EMPTY] 0
00:15:00 0:18:44 5.02
[EMPTY] 0:18:45 -0.02
00:30:00 [EMPTY] 0
00:45:00 [EMPTY] 0
01:00:00 [EMPTY] 0
01:15:00 1:16:59 2.52
[EMPTY] 1:17:00 -0.04

--------------------Does that help?--
A S-D
------------------------------------------------------------------------
A S-D's Profile: www.excelforum.com/member.php...oamp;userid=33227
View this thread: www.excelforum.com/showthread...hreadid=530452
So, as per sheet 2 of the attached is a count of items, is that how you
would see your data?

using
=COUNTIF(Test1!C2:C409,D2)

--

A S-D Wrote:
gt; Again, thanks for looking at this problem. I wish I could explain this
gt; more simply!
gt;
gt; Bryan, the column C is merely the whole testing period split into 15
gt; minute intervals. It is what I want my data ordered by:
gt;
gt; Cumulative
gt; Time 15 minute
gt; intervals Voltage of turn.
gt; 0:00:00 00:00:00 -0.06
gt; 0:18:44 00:15:00 5.02
gt; 0:18:45 00:30:00 -0.02
gt; 1:16:59 00:45:00 2.52
gt; 1:17:00 01:00:00 -0.04
gt;
gt; Should become:
gt;
gt;
gt; gt;
Code:
--------------------
gt; gt;
gt; 15 Minute Intervals // Cumulative Time // Voltage of Turn (rest of data)
gt;
gt; 00:00:00 0:00:00 -0.06
gt; 00:15:00 0:18:44 5.02
gt; [EMPTY] 0:18:45 -0.02
gt; 00:30:00 [EMPTY] 0
gt; 00:45:00 [EMPTY] 0
gt; 01:00:00 [EMPTY] 0
gt; 01:15:00 1:16:59 2.52
gt; [EMPTY] 1:17:00 -0.04
gt;
--------------------
gt; gt;
gt;
gt; Does that help?--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: www.excelforum.com/member.php...oamp;userid=21059
View this thread: www.excelforum.com/showthread...hreadid=530452
I'm sorry? Which sheet 2? What count?

I think I've missed something.--
A S-D
------------------------------------------------------------------------
A S-D's Profile: www.excelforum.com/member.php...oamp;userid=33227
View this thread: www.excelforum.com/showthread...hreadid=530452
or, per your example, see the third sheet

---Bryan Hessey Wrote:
gt; So, as per sheet 2 of the attached is a count of items, is that how you
gt; would see your data?
gt;
gt; using
gt; =COUNTIF(Test1!C2:C409,D2)
gt;
gt; ----
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: www.excelforum.com/member.php...oamp;userid=21059
View this thread: www.excelforum.com/showthread...hreadid=530452uHi,

Try this. I have a completed w/book but it is to large to post to
excelform. If you give me an e-mail address I will post.Sub a()
Dim lastrow As Long, r As Long
Dim interval As Long
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Worksheets(quot;Test Rat 080206-090206quot;)
Set ws2 = Worksheets(quot;Updated Test Rat 080206-090206quot;)
With ws1
lastrow = .Cells(Rows.Count, quot;Bquot;).End(xlUp).Row
rc = 2
rr = 2
r = 2
Do
If Application.And(.Cells(r, quot;Bquot;) gt;= .Cells(rc, quot;Cquot;), _
.Cells(r, quot;Bquot;) lt;= .Cells(rc 1, quot;Cquot;)) Then
.Cells(r, quot;Aquot;).EntireRow.Copy ws2.Cells(rr, quot;Aquot;)
ws2.Cells(rr, quot;Cquot;) = .Cells(rc, quot;Cquot;)
r = r 1
rr = rr 1
Else
If lr = r Then rr = rr 1
rc = rc 1
ws2.Cells(rr, quot;Cquot;) = .Cells(rc, quot;Cquot;)
lr = r
End If
Loop Until r gt; lastrow
End With
End SubHTHquot;A S-Dquot; wrote:

gt;
gt; I'm sorry? Which sheet 2? What count?
gt;
gt; I think I've missed something.
gt;
gt;
gt; --
gt; A S-D
gt; ------------------------------------------------------------------------
gt; A S-D's Profile: www.excelforum.com/member.php...oamp;userid=33227
gt; View this thread: www.excelforum.com/showthread...hreadid=530452
gt;
gt;