data missing in a seris of dates.... anybody can help?

[AmandaSS]

Hi guys, I need a help with this time series and list of figure.

I have a column of dates and hours. To each of them corresponds a figure.

column A	column B
01/01/2011 00:00	40
01/01/2011 01:00	-98
01/01/2011 06:00	55
01/01/2011 18:00	45

<tbody>
</tbody>

Dates and hours are organized from the oldest to the most recent, but sometimes hours / dates are missing. I need to fill column B with zeros for those hours where the figure is missing.

column A	column B
01/01/2011 00:00	40
01/01/2011 01:00	-98
01/01/2011 02:00	0
01/01/2011 03:00	0
01/01/2011 04:00	0
01/01/2011 05:00	0
01/01/2011 06:00	55
.....	0
....

<tbody>
</tbody>

thanks a lot!
Best
Amanda

 

[AmandaSS]

Hi I am back to this post because I need to do the same with a new data set.
The only difference this time is that I had dates and hours separated in 2 cells and to run the VBA I had to combine the two cells in one, which I did by using this formula:

=TEXT(A2,"dd/mm/yyyy")&" "&TEXT(B2,"hh:mm:ss")

When I then run the VBA i have a debug at the 8th line: If c(i + 1, 1) - c(i, 1) > 1 / 24 Then

What is wrong with this coding?

Thanks,
Amanda

 

[pbornemeier]

First verify that the columns that are showing the dates and times contain the actual date and time values and are not just showing them due to formatting (both could contain the same date/time stamp, but be formatted differently).
Assuming Dates are in A and times are in B.
Format Columns A & B to "0.000000000" and see if they all change to numbers, if not the are text and have to be changed to numbers by using copy/paste special to multiply all of them by 1
in a helper column (M for example) use this formula in M2 and copy it down: =A2=INT(A2)
in a helper column (N for example) use this formula in N2 and copy it down: =B2<1
All values in columns M:N should be TRUE
Delete helper columns

Combine Dates & Times
If your days are in column A and your hours are in column B, insert a new column A then put this formula in cell A2 and copy it down:
=1*B2+1*C2
Copy paste special values in column A to change the formula to values
Format column B as a date and C as time
Format column A with "dd/mm/yyyy hh:mm:ss" and compare a few of them to the values in B & C to verify that the values match.
Delete columns B & C and run the prevuious code.

 

[AmandaSS]

Done
It worked perfectly
thanks a lot!

 

[AmandaSS]

Hi again,

what if I had one column with date/hours and other 8 columns with figures (with some missing figure every now and then)?

date/hour	x1	x2	x3	x4	x5	x6	x7	x8
01/2013 00:00	4	3	6	8	2	79	4	2
01/2013 02:00	9	8	3	3	6	7	9	1

<tbody>
</tbody>

and I need

date/hour	x1	x2	x3	x4	x5	x6	x7	x8
01/2013 00:00	4	3	6	8	2	79	4	2
01/2013 01:00	0	0	0	0	0	0	0	0
01/2013 02:00	9	8	3	3	6	7	9	1

<tbody>
</tbody>

thanks,
Amanda

 

[pbornemeier]

Ned code with some surprise features!
Constants are set for Sort Data, Has Headers, Color Inserted Rows. Change them to false if not desired.

Option Explicit

Sub SortThenInsertHourRowsWithZeroValues()

    Const bSort As Boolean = True   'True to sort, False to not sort
    Const bHeader As Boolean = True 'True if data has headers, False if no headers
    Const bColor As Boolean = True  'True to color inserted row column A, False to not color

    Dim lLastDataRow As Long
    Dim lLastDataColumn As Long
    Const sngHourValue As Single = 1 / 24
    Dim lX As Long
    Dim lActiveRow As Long
    
    'Sort the data to be ensure what is there is in date/time order
    With ActiveSheet
    
        lLastDataColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
        
        If bSort Then
            lLastDataRow = .Cells(.Rows.Count, 1).End(xlUp).Row
            
            .Sort.SortFields.Clear
            .Sort.SortFields.Add Key:=.Range("A2:A" & lLastDataRow), _
                SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            With .Sort
                .SetRange ActiveSheet.Range(ActiveSheet.Cells(1, 1), ActiveSheet.Cells(lLastDataRow, lLastDataColumn))
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
        End If
    
        lLastDataRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        
        lActiveRow = lLastDataRow
        Do While lActiveRow > IIf(bHeader, 2, 1)
            If (.Cells(lActiveRow, 1) - sngHourValue) - .Cells(lActiveRow - 1, 1) > 0.01 Then
                .Cells(lActiveRow, 1).EntireRow.Insert
                .Cells(lActiveRow, 1).Value = .Cells(lActiveRow + 1, 1) - sngHourValue
                .Range(.Cells(lActiveRow, 2), .Cells(lActiveRow, lLastDataColumn)).Value = 0
                If bColor Then .Cells(lActiveRow, 1).Interior.Color = vbCyan
                lActiveRow = lActiveRow + 1
            End If
            lActiveRow = lActiveRow - 1
        Loop
        
    End With
End Sub

 

[AmandaSS]

Hi! I am backfor the 3rd time to this code.
I have

hours	hours	fugures
1	2	7
2	23	37
....	..	..
23	986	612
...	3263	21
8760	6587	987

<tbody>
</tbody>

the first coulumn contains increasing hours, from 1 to 8760 without missing hours. The second coulmn contatins X number of hours in an increasing trend, but with some missing hours. The 3rd coulumn contains fugures associated to the second coulmn. what I need is to associate the second and corresponding 3rd coulumn to the 1rst coulumn, and leaving blank the cells where threre is no correspondance between the first andthe second coulumn. Like below:

hours	hours	fugures
1	-	-
2	2	7
...	...	...
23	23	37
...	..	..
986	986	62
8760	-	-

<tbody>
</tbody>

Thanks
Amanda

 

[pbornemeier]

Try

Option Explicit

Sub InsertRows()

    Dim lLastDataRow As Long
    Dim lX As Long
    Dim lActiveRow As Long
    Dim lCheck As Long
    
    lLastDataRow = Cells(Rows.Count, 2).End(xlUp).Row
    lActiveRow = lLastDataRow
    Do While lActiveRow > 2
        If Cells(lActiveRow, 2).Value - Cells(lActiveRow - 1, 2).Value <> 1 Then
            Range(Cells(lActiveRow, 2), Cells(lActiveRow, 3)).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
            Cells(lActiveRow, 2).Value = Cells(lActiveRow + 1, 2).Value - 1
            lActiveRow = lActiveRow + 1
        End If
        lActiveRow = lActiveRow - 1
    Loop
    lCheck = Cells(2, 2).Value
    If lCheck <> 1 Then
        Range(Cells(2, 2), Cells(lCheck, 3)).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        With Range(Cells(2, 2), Cells(lCheck, 2))
            .FormulaR1C1 = "=RC[-1]"
            .Value = .Value
        End With
    End If
End Sub

 

[AmandaSS]

Great! infinite thanks

 

[AmandaSS]

Hi I implemented the code and I noticed 2 issues.

A	B	C	D	E
hours (8760)	hours (in a non sequential order)	figure	results	figures (associate to the corresponding hour in a sequential order)
1	h10	49.41176	10	49.41176471
2	h11	140	2
3	h12	140	3
4	h14	140	4
5	h27	49.41176	5
6	h28	140	6
7	h29	140	7
8	h51	49.41176	8
9	h52	140	9
10	h53	140	10
11	h61	140	11
12	h62	140	11	140
13	h75	49.41176	12	140
14	h76	140	13
15	h77	140	14	140
16	h85	24.70588	15

<tbody>
</tbody>

There are 2 problems in the results (I highlighted them in red).

1)
In coloumn D, where the results start, I have 10 as first hour with the corresponding figure (49.41176) but in the wrong row. Whereas in correspondence with hours 10 of the results I dont have the right figure.

2)
In column D, hour 11 repeats twice.

Do you know what could be the error?

Thanks.
Best
Amanda

 

[pbornemeier]

Please provide the first 20 rows of data that you applied the code to to get the results you posted.