Column matching - sorting. Fairly hard problem, I think.
 

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: http://www.excelforum.com/attachment.php?postid=4592 |
+-------------------------------------------------------------------+

--
A S-D
------------------------------------------------------------------------
A S-D's Profile: http://www.excelforum.com/member.php...o&userid=33227
View this thread: http://www.excelforum.com/showthread...hreadid=530452

Column matching - sorting. Fairly hard problem, I think.
 

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: http://www.excelforum.com/member.php...o&userid=31996
View this thread: http://www.excelforum.com/showthread...hreadid=530452

Column matching - sorting. Fairly hard problem, I think.
 

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: http://www.excelforum.com/member.php...o&userid=33227
View this thread: http://www.excelforum.com/showthread...hreadid=530452

Column matching - sorting. Fairly hard problem, I think.
 

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:
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: Lab Rat 080206-090206 Complete.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4593 |
+-------------------------------------------------------------------+

--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=530452

Column matching - sorting. Fairly hard problem, I think.
 

Hi,
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
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


"A S-D" wrote:


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: http://www.excelforum.com/member.php...o&userid=33227
View this thread: http://www.excelforum.com/showthread...hreadid=530452

Column matching - sorting. Fairly hard problem, I think.
 

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: http://www.excelforum.com/member.php...o&userid=33227
View this thread: http://www.excelforum.com/showthread...hreadid=530452

Column matching - sorting. Fairly hard problem, I think.
 

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:
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 0:00:00 -0.06
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?


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=530452

Column matching - sorting. Fairly hard problem, I think.
 

I'm sorry? Which sheet 2? What count?

I think I've missed something.


--
A S-D
------------------------------------------------------------------------
A S-D's Profile: http://www.excelforum.com/member.php...o&userid=33227
View this thread: http://www.excelforum.com/showthread...hreadid=530452

Column matching - sorting. Fairly hard problem, I think.
 

or, per your example, see the third sheet

---


Bryan Hessey Wrote:
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)

--


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=530452

Column matching - sorting. Fairly hard problem, I think.
 

uHi,

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("Test Rat 080206-090206")
Set ws2 = Worksheets("Updated Test Rat 080206-090206")
With ws1
lastrow = .Cells(Rows.Count, "B").End(xlUp).Row
rc = 2
rr = 2
r = 2
Do
If Application.And(.Cells(r, "B") = .Cells(rc, "C"), _
.Cells(r, "B") <= .Cells(rc + 1, "C")) Then
.Cells(r, "A").EntireRow.Copy ws2.Cells(rr, "A")
ws2.Cells(rr, "C") = .Cells(rc, "C")
r = r + 1
rr = rr + 1
Else
If lr = r Then rr = rr + 1
rc = rc + 1
ws2.Cells(rr, "C") = .Cells(rc, "C")
lr = r
End If
Loop Until r lastrow
End With
End Sub


HTH


"A S-D" wrote:


I'm sorry? Which sheet 2? What count?

I think I've missed something.


--
A S-D
------------------------------------------------------------------------
A S-D's Profile: http://www.excelforum.com/member.php...o&userid=33227
View this thread: http://www.excelforum.com/showthread...hreadid=530452

Column matching - sorting. Fairly hard problem, I think.
 

Thank you Brian and Toppers.

Brian, I can't check your spread-sheet (I've moved to my domestic
computer without Excel). Toppers, I've sent you my address in a private
message. Thanks for all your effort, guys.

Appreciatively,

Adam


--
A S-D
------------------------------------------------------------------------
A S-D's Profile: http://www.excelforum.com/member.php...o&userid=33227
View this thread: http://www.excelforum.com/showthread...hreadid=530452

Column matching - sorting. Fairly hard problem, I think.
 

Hey.

After all that effort, it seems that only half the problem is solved. I
now need to average the turning behaviour over each 15 minute interval.
I had planned to do this by hand, but I have another 15-25 spreadsheets
that need analysing in this way, so I don't think it would be very
efficient. In the spreadsheet I want to average the behaviour for each
turning category: "Standstill", "Slow Left", "Slow Right", "Fast Left",
and "Fast Right".

Another point: How do I extract this data manipulation to other sheets?
How do I apply the VB code to another spreadsheet that is arrayed in the
same way as the first was. The intervals are always 15 minutes, but the
duration will occasionally be over 24 hours.

You guys are under no obligation to help me in this! I'm already very
grateful for what you've suggested.

I'll e-mail the sheet, as is, to Bryan and Topper. Don't feel obliged
to respond!

Adam


--
A S-D
------------------------------------------------------------------------
A S-D's Profile: http://www.excelforum.com/member.php...o&userid=33227
View this thread: http://www.excelforum.com/showthread...hreadid=530452

Column matching - sorting. Fairly hard problem, I think.
 

Adam,

The attached should produce the required sheet as per the code supplied
by Toppers, and the column K fix.

Simply Tools, Macros, Macro and Run the macro.

This will populate the new worksheet.

To do this to other worksheets you need to copy the macro and amend the
names of the sheets on lines 5 and 6.

To see the code you can:
1. press Alt/F11 or
2. rightmouse the sheet-name-tab and select View Code or
3. Tools, Macros, Macro, Edit

Select and copy all of the code, and 'view code' on the next sheet and
Paste the code.
Insert a new sheet and rename it to 'Updated ~~~ etc' then within the
just pasted locate and amend the two sheetnames to be the sheets you
are now using.

Then Tools, Macros, Macro and select and run the new copy.

This should re-produce the the workbook that Toppers sent you, and you
can then do the remaining workbooks.

Am still testing how to average over 15 min periods.

Bryan

A S-D Wrote:
Hey.

After all that effort, it seems that only half the problem is solved. I
now need to average the turning behaviour over each 15 minute interval.
I had planned to do this by hand, but I have another 15-25 spreadsheets
that need analysing in this way, so I don't think it would be very
efficient. In the spreadsheet I want to average the behaviour for each
turning category: "Standstill", "Slow Left", "Slow Right", "Fast Left",
and "Fast Right".

Another point: How do I extract this data manipulation to other sheets?
How do I apply the VB code to another spreadsheet that is arrayed in the
same way as the first was. The intervals are always 15 minutes, but the
duration will occasionally be over 24 hours.

You guys are under no obligation to help me in this! I'm already very
grateful for what you've suggested.

I'll e-mail the sheet, as is, to Bryan and Topper. Don't feel obliged
to respond!

Adam


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

--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=530452

Column matching - sorting. Fairly hard problem, I think.
 

After you produce the Updated sheet,

Format column C, W, X, Y and Z as [hh]:mm:ss
Format column S, T, U and V as number, no decimal places.

In S2 put the formula

=IF($B2="","",IF(N2=0,S1,IF($C2<$C1,1,IF(S1="",1, S1+1))))

and formula drag that acros T2, U2 and V2

In W2 put the formula

=IF($B2="","",IF(N2=0,W1,IF($C2<$C1,N2,IF(W1="",N 2,W1+N2))))

and formula drag that across X2, Y2 and Z2

Select (highlight) S2-Z2 and formula drag the 8 column formula to the
end of your data.

This gives you the Count of items per 15 minute period, and the Sum per
15 minute period, you need to divide W2 by S2, X2 by T2, Y2 by U2 and Z2
by V2 etc to obtain the average.

Hope this helps
--

Bryan Hessey Wrote:
Adam,

The attached should produce the required sheet as per the code supplied
by Toppers, and the column K fix.

Simply Tools, Macros, Macro and Run the macro.

This will populate the new worksheet.

To do this to other worksheets you need to copy the macro and amend the
names of the sheets on lines 5 and 6.

To see the code you can:
1. press Alt/F11 or
2. rightmouse the sheet-name-tab and select View Code or
3. Tools, Macros, Macro, Edit

Select and copy all of the code, and 'view code' on the next sheet and
Paste the code.
Insert a new sheet and rename it to 'Updated ~~~ etc' then within the
just pasted locate and amend the two sheetnames to be the sheets you
are now using.

Then Tools, Macros, Macro and select and run the new copy.

This should re-produce the the workbook that Toppers sent you, and you
can then do the remaining workbooks.

Am still testing how to average over 15 min periods.

also, you need to format column C to [hh]:mm:ss

Bryan


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=530452