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

 

[pbornemeier]

I am assuming a header row.
In post #19, column B is shown to contain numbers like h28. Is that a text value?
I modified the code a bit to handle blank lines at end, but the main part is the same. It works for me with the test data below. Are all of the data values in columns A & B integers?

Option Explicit

Sub InsertRows()
    'Assumes there is a header row and data starts in row 2
    
    Dim lLastColumnBRow As Long
    Dim lLastColumnARow As Long
    Dim lX As Long
    Dim lActiveRow As Long
    Dim lCheck As Long
    
    lLastColumnBRow = Cells(Rows.Count, 2).End(xlUp).Row
    lActiveRow = lLastColumnBRow
    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
    
    'Need more at beginning?
    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
    
    'Need more at end?
    lLastColumnARow = Cells(Rows.Count, 1).End(xlUp).Row
    lLastColumnBRow = Cells(Rows.Count, 2).End(xlUp).Row
    If lLastColumnARow > lLastColumnBRow Then
        Cells(lLastColumnBRow, 2).DataSeries Rowcol:=xlColumns, _
            Type:=xlLinear, Date:=xlDay, Step:=1, _
            Stop:=lLastColumnARow - 1, Trend:=False
    End If
    
End Sub

Test Data:

A	B	C
1	3	333
2	7	777
3	9	999
4		
5		
6		
7		
8		
9		
10

 

[AmandaSS]

Hi,

I am back to this code.
I now have one column with the complete time frame (hours from 1 to 8760), a column with hours in an increasing order but with missing hours (h1, h5, h6, h7, h988, h1000, ect..) and 10 more columns with figures associated to each hour in the second column.

What I have to do is to associate the last 11 columns (incomplete column of hours + associated 10 columns with figures) with the first one.

PS: figures contained in the 10 columns are not integers. They are all 1.366 or 11997.333 or 0.0022.

I tried the code above but it seams like it is targeted to work only in case of 3 columns in total.

Cheers,
Amanda

 

[AmandaSS]

Hi,

I am back to this code.
I now have one column with the complete time frame (hours from 1 to 8760), a column with hours in an increasing order but with missing hours (h1, h5, h6, h7, h988, h1000, ect..) and 10 more columns with figures associated to each hour in the second column.

What I have to do is to associate the last 11 columns (incomplete column of hours + associated 10 columns with figures) with the first one.

PS: figures contained in the 10 columns are not integers. They are all 1.366 or 11997.333 or 0.0022.

I tried the code above but it seams like it is targeted to work only in case of 3 columns in total.

Cheers,
Amanda

for example:
what I have (in the real data set: 12 columns and 8761 rows)

complete time	hours	fig1	fig2	fig3	fig4	fig5	fig6	fig7	fig8	fig9	fig10
1	2	99	3.2		13.7		12	66.04
2	3			0.005	1.98				130.98		2
3	4		1.09		135	44				2
4

<tbody>
</tbody>

what I need

complete time	hours	fig1	fig2	fig3	fig4	fig5	fig6	fig7	fig8	fig9	fig10
1	1
2	2	99	3.2		13.7		12	66.04
3	3			0.005	1.98				130.98		2
4	4		1.09		135	44				2

<tbody>
</tbody>

 

[pbornemeier]

This modification will work for the number of columns that are in Row 1.
Test on a sample of your data. Let me know if any other changes are required.

Option Explicit

Sub InsertRows()
    'Assumes there is a header row and data starts in row 2
    
    Dim lLastColumnBRow As Long
    Dim lLastColumnARow As Long
    Dim lX As Long
    Dim lActiveRow As Long
    Dim lCheck As Long
    Dim lLastDataColumn As Long
    
    lLastColumnBRow = Cells(Rows.Count, 2).End(xlUp).Row
    lLastDataColumn = Cells(1, Columns.Count).End(xlToLeft).Column
    
    'If for some reason you want to leave some of the rows at the end
    'to be excluded from the insert, uncomment the next row and change
    'the value to the desired column count.
    'lLastDataColumn = 12
    
    lActiveRow = lLastColumnBRow
    Do While lActiveRow > 2
        If Cells(lActiveRow, 2).Value - Cells(lActiveRow - 1, 2).Value <> 1 Then
            Range(Cells(lActiveRow, 2), Cells(lActiveRow, lLastDataColumn)).Insert _
                Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
            Cells(lActiveRow, 2).Value = Cells(lActiveRow + 1, 2).Value - 1
            lActiveRow = lActiveRow + 1
        End If
        lActiveRow = lActiveRow - 1
    Loop
    
    'Need more at beginning?
    lCheck = Cells(2, 2).Value
    If lCheck <> 1 Then
        Range(Cells(2, 2), Cells(lCheck, lLastDataColumn)).Insert _
            Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        With Range(Cells(2, 2), Cells(lCheck, 2))
            .FormulaR1C1 = "=RC[-1]"
            .Value = .Value
        End With
    End If
    
    'Need more at end?
    lLastColumnARow = Cells(Rows.Count, 1).End(xlUp).Row
    lLastColumnBRow = Cells(Rows.Count, 2).End(xlUp).Row
    If lLastColumnARow > lLastColumnBRow Then
        Cells(lLastColumnBRow, 2).DataSeries Rowcol:=xlColumns, _
            Type:=xlLinear, Date:=xlDay, Step:=1, _
            Stop:=lLastColumnARow - 1, Trend:=False
    End If
    
End Sub

Sorry to see your profile picture go. It was quite memorable.

 

[AmandaSS]

Hi, I am back to this treat for a similar issue.

I have a matrix of 8761 columns and 40 rows. Columns stand for the hours in a year (8760+1heading).
Rows contains some figures in correspondence with each column.

I need to combine another matrix of xxxx columns (<8761) and xx rows with some figures in correspondence with each column, where the columns have the same heading as in the first matrix, but with some missing hours.
I need to associate the 2 matrix according to the hours.

I give a simplified example.

first matrix

heading	h1	h2	h3	h4	h5
row A	5	3	0	0	1
row B	0	0	1	1	3
row C	0	2	3	1	1

<tbody>
</tbody>

second matrix

heading	h1	h3	h5
row D	4	6	3
row E		1	0

<tbody>
</tbody>

What I need is:

heading	h1	h2	h3	h4	h5
row A	5	3	0	0	1
row B	0	0	1	1	3
row C	0	2	3	1	1
row D	4		6		3
row E			1		0

<tbody>
</tbody>

Thanks!
Amanda

PS: Phil, I've just read your text at the end of the code. I didnt even remember I had a profile pic

 

[pbornemeier]

A restatement to be sure I understand the situation:

You want the second matrix columns to be added to the rows below the first, adjusted so its columns line up with the corresponding headers/data columns in the first matrix.

The first matrix headers are 1...8760 starting in cell B1 and continuing right in ascending order, with no blank columns or blank rows.
The first matrix always contains 40 rows. Does it end on row 40 (39 rows of data) or 41 (40 rows of data)?

The second matrix contains 8760 or fewer columns, each headed with a unique number between 1 and 8760, in ascending order, with no blank columns or blank rows.
The second matrix contains a number of rows, which may vary between iterations.

What is the name of the sheet that contains the first matrix?
What is the name of the sheet that contains the second matrix?

The example does not adhere to my assumptions so I need to get confirmation of them.
It has the headers on second matrix (before the merge) arranged such that h1 is in C2, h3 is in C3 and h5 is in C4.



the profile pic was similar to:
Businesswoman Sitting On Desk In Splits Using Laptop Computer Stock Photo | Getty Images