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

[A S-D]

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 |
+-------------------------------------------------------------------+

 

[robert111]

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

 

[A S-D]

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 fall
into problems when an interval would have no entries.

I have data from a device. This device records the RApid Turn of som
apparatus, through a varying voltage. Each time the voltage change
(i.e. the device detects a turn) an entry is made with the appropriat
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 th
start. How do I make it so that the timestamp maps itself to the 1
minute intervals? In some intervals, there was no activity at all, s
that interval should have no corresponding timestamps.

Is this even possible in Excel

 

[Bryan Hessey]

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

--

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 |
+-------------------------------------------------------------------+

 

[Guest]

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]

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

 

[Bryan Hessey]

So, as per sheet 2 of the attached is a count of items, is that how yo
would see your data?

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

--

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

Bryan, the column C is merely the whole testing period split into 1
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

 

[A S-D]

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

I think I've missed something.

 

[Bryan Hessey]

or, per your example, see the third sheet

---

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)

--

 

[Guest]

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]

Thank you Brian and Toppers.

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

Appreciatively,

Ada

 

[Bryan Hessey]

Toppers,

That's great, took a minute to realise the 'Subscript out of range'
meant that I needed to manually insert the second sheet, but works
fine. Is there another way to convert the formula at column K,
something like this added to your code:

-Loop Until r > lastrow
End With-

With ws2
lastrow = .Cells(Rows.Count, "B").End(xlUp).Row
r = 2
Testrows:
If .Range("B" & r).Value = "" And r < lastrow Then
r = r + 1
GoTo Testrows
End If

rr = r + 1 'find the next non-blank

nextrow:
If .Range("B" & rr).Value = "" And rr < lastrow Then
rr = rr + 1
GoTo nextrow
End If

If .Range("B" & r).Value <> "" And r < lastrow Then
Range("K" & r).Formula = "=Sum( B" & rr & "-B" & r & ")"
End If

If r < lastrow - 1 Then
r = r + 1
GoTo Testrows
End If

-End With

End Sub-


This adjusts the K formua to the next non-inserted line, but my VB code
is not that hot (it just works, and only just)

HTH
--

 

[A S-D]

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

 

[Bryan Hessey]

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

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]

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="",N2,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
--

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