Need to move cells in a column based on date in other column

[edwardewilliams]

Hi folks,

Here's my conundrum. I have three sets of data points, all referenced to time/date - but not precisely the SAME time/date. One set of points is recorded once each minute, one set is recorded when a temperature changes and a third set is recorded based on a change of state "0/1".

I need to make a line graph with all three sets of data lined up based on their timestamps. To do this, I need to be able to have the rows of my sheet lined up by timestamp.

So, what I have is something like this:

         A           B            C            D              E             F
1  8/7/07 0:31     135.5     8/7/07 0:31      78.8       8/7/07  0:31       0
2  8/7/07 0:32     137.6     8/7/07 0:35      79.0       8/7/07  0:37       1
3  8/7/07 0:33     138.2     8/7/07 0:39      79.1       8/7/07  0:41       0
4  8/7/07 0:33     139.5     8/7/07 0:42      79.4       8/7/07  0:44       1
5  8/7/07 0:34     139.5     8/7/07 0:51      79.5       8/7/07  0:46       0
...etc

and this is what I need to generate:

         A            B            C            D              E             F
1   8/7/07 0:31     135.5     8/7/07 0:31      78.8       8/7/07  0:31       0
2   8/7/07 0:32     137.6     
3   8/7/07 0:33     138.2     
4   8/7/07 0:33     139.5                                         
5   8/7/07 0:34     139.5     
6   8/7/07 0:35     139.9     8/7/07 0:35      79.0      
7   8/7/07 0:36     140.0
8   8/7/07 0:37     141.1                                 8/7/07  0:37       1
9   8/7/07 0:38     141.0
10  8/7/07 0:39     140.0     8/7/07 0:39      79.1
...etc

If it were only a few data points, doing this manually by dragging groups of cells and lining them up would be an option. I've got approximately 3500 data points in the first series, however, and almost that many in the second and third. I'm looking for an automated way to line up the rows of the second and third series so they match up with the appropriate rows of the first series based on the timestamp.

Thanks!
Ed

 

[roentgen]

There is a free table alignment utility at:

http://www.glynnconsulting.co.uk/Utils.htm

It works on two tables, but the vba code could easily be adapted to work on more.

 

[zzjasonzz]

Right click on the sheet tab go view code, then right click on microsoft excel object on the left and go Insert > Module then paste this

Sub align()
Dim i
For i = 2 To Range("A2").End(xlDown).Row
    If Range("A" & i) <> Range("C" & i) Then
        Range("C" & i & ":D" & i).Insert Shift:=xlDown
    End If
    If Range("A" & i) <> Range("E" & i) Then
        Range("E" & i & ":F" & i).Insert Shift:=xlDown
    End If
Next i
End Sub

Situations when this code will fail:
- If there is a value in C that isnt in A (for example if 12:35 was missing from column A what would happen?)
- If there is blank spaces in A
- If there are two of the same value in column C/E
- If the data is not sorted


If any of these are a problem then let me know

 

[edwardewilliams]

Right click on the sheet tab go view code, then right click on microsoft excel object on the left and go Insert > Module then paste this

Sub align()
Dim i
For i = 2 To Range("A2").End(xlDown).Row
    If Range("A" & i) <> Range("C" & i) Then
        Range("C" & i & ":D" & i).Insert Shift:=xlDown
    End If
    If Range("A" & i) <> Range("E" & i) Then
        Range("E" & i & ":F" & i).Insert Shift:=xlDown
    End If
Next i
End Sub

Situations when this code will fail:
- If there is a value in C that isnt in A (for example if 12:35 was missing from column A what would happen?)
- If there is blank spaces in A
- If there are two of the same value in column C/E
- If the data is not sorted


If any of these are a problem then let me know

PERFECT!! Thank you very much. I appreciate the assistance.