Help please

[Shankar08]

Dear all,

I am a newbie with limited experience in using excel or VBA. Any help in with this problem I have will be much appreciated. I have spent good few days trying to solve this but could not. I am testing a device that monitors the leg movements over night and gives out an excel sheet with the particular times when a movement occurs.
For example:
A1: 24/06/2011 22:30:00 B1: M (indicating movement)
A2: 24/06/2011 22:33:00 B2: M...........................
A90: 25/06/2011 06:29:00 B90: M

I need to integrate these random times into a timeline (if I can call it) so that I will have an array with consecutive minutes that will tell me if that particular minute is associated with a movement (M) or not (0).
For example
A1: 24/06/2011 22:29:00 B1: 0
A2: 24/06/2011 22:30:00 B2: M
A3: 24/06/2011 22:31:00 B3: 0
A4: 24/06/2011 22:32:00 B4: 0
A5: 24/06/2011 22:33:00 B5: M
A6: 24/06/2011 22:34:00 B6: 0........
A89: 25/06/2011 06:28:00 B89: 0
A90: 25/06/2011 06:29:00 B90: M........


Many thanks.
Shankar.

 

[Robert Mika]

Hi Shankar and welcome to the board.

Do you have more than one spreasdheet?
Your data in the top table and bottom table look very similar.
Could you elaborate your question a little bit more?

 

[Sanjeev1976]

Dear all,

I am a newbie with limited experience in using excel or VBA. Any help in with this problem I have will be much appreciated. I have spent good few days trying to solve this but could not. I am testing a device that monitors the leg movements over night and gives out an excel sheet with the particular times when a movement occurs.
For example:
A1: 24/06/2011 22:30:00 B1: M (indicating movement)
A2: 24/06/2011 22:33:00 B2: M...........................
A90: 25/06/2011 06:29:00 B90: M

I need to integrate these random times into a timeline (if I can call it) so that I will have an array with consecutive minutes that will tell me if that particular minute is associated with a movement (M) or not (0).
For example
A1: 24/06/2011 22:29:00 B1: 0
A2: 24/06/2011 22:30:00 B2: M
A3: 24/06/2011 22:31:00 B3: 0
A4: 24/06/2011 22:32:00 B4: 0
A5: 24/06/2011 22:33:00 B5: M
A6: 24/06/2011 22:34:00 B6: 0........
A89: 25/06/2011 06:28:00 B89: 0
A90: 25/06/2011 06:29:00 B90: M........


Many thanks.
Shankar.

Welcome to the board.

Without complicating too much, I'll try and give you a simple solution. You can add the starting time in the column next to your data say if your data is in column A and B, you can put the starting time in cell D1. Then you can put this formula in cell D2 =D1+1/1440. What you are really doing is adding a minute to the starting time in cell D1. Copy this formula to the ending time you want. In cell E1, use this formula to check whether there is data in cell A and B =IF(ISERROR(INDEX($A$1:$B$8,MATCH(D1,$A$1:$A$8,0),2)),0,INDEX($A$1:$B$8,MATCH(D1,$A$1:$A$8,0),2)).

Modify the cell references with the actual data.

 

[Shankar08]

Dear Robert,

Thanks for your reply. The data is in a single sheet. The top table I showed was that produced by the device. The bottom table is what I would like to produce fitting the events in a timeline.

Shankar.

<table border="0" cellpadding="0" cellspacing="0" height="21" width="111"><tbody><tr height="20"><td style="height:15.0pt;width:83pt" align="right" height="20" width="111">
</td> </tr></tbody></table>​

 

[Shankar08]

Dear Sanjeev,

Thanks for your prompt reply. Your formula should work but the data in the column produced by the device and the column we made by adding 1/1440 differ in actual value ever so slightly, for example 26/05/2011 22:22:41 (by adding 1/1440 you get 40689.9451388889 compared to 40689.9451388888 obtained from the device). As such they are not exact match. I tried to round it to 5 decimals and copy it to a different column but what it actually copies is the exact value prior to rounding. Hmmm.......help please.
<table border="0" cellpadding="0" cellspacing="0" height="21" width="111"><colgroup><col width="111"></colgroup><tbody><tr height="20"> <td style="height:15.0pt;width:83pt" align="right" height="20" width="111">
</td></tr></tbody></table>Shankar.

 

[Sanjeev1976]

Dear Sanjeev,

Thanks for your prompt reply. Your formula should work but the data in the column produced by the device and the column we made by adding 1/1440 differ in actual value ever so slightly, for example 26/05/2011 22:22:41 (by adding 1/1440 you get 40689.9451388889 compared to 40689.9451388888 obtained from the device). As such they are not exact match. I tried to round it to 5 decimals and copy it to a different column but what it actually copies is the exact value prior to rounding. Hmmm.......help please.
<TABLE border=0 cellSpacing=0 cellPadding=0 width=111 height=21><COLGROUP><COL width=111></COLGROUP><TBODY><TR height=20><TD style="WIDTH: 83pt; HEIGHT: 15pt" height=20 width=111 align=right>

</TD></TR></TBODY></TABLE>Shankar.

Use =ROUND(A1*1440,0)/1440 to round the time from your device

 

[Shankar08]

You are a star!!!That solved the problem.
If not asking for too much can this be put in a macro as I need to perform this on around 200 to 250 sheets!!! Once again thanks for your help.

Shankar.

 

[Sanjeev1976]

You are a star!!!That solved the problem.
If not asking for too much can this be put in a macro as I need to perform this on around 200 to 250 sheets!!! Once again thanks for your help.

Shankar.

If the data is not confidential you can send me the file on sanjeevrjain@gmail.com to check the file structure and prepare VBA