Automatically extrapolating data?

[JeremyBowyer]

Hi guys, I've been lurking for a while and have gotten a lot of valuable information from the advice given by you folks, so thanks for that.

I have what I expect will be a simple problem (if there's a way to solve it). First off I'm working in Excel 2013 64-bit. Basically I'm going to have large amounts of data with gaps that I need to fill in using what I know from the data I do have. That's not very clear so let me give you an example.


My data looks like this:

Year	Country	X	Y
1901	Germany	1	11
1902	Germany
1903	Germany
1904	Germany
1905	Germany		15
1906	Germany
1907	Germany
1908	Germany
1909	Germany
1910	Germany	10	10
1911	Germany
1912	Germany
1913	Germany		7
1914	Germany
1915	Germany
1916	Germany
1917	Germany
1918	Germany		12
1919	Germany
1920	Germany	20	14

<tbody>
</tbody>

I want it to look like this:

Year	Country	X	Y
1901	Germany	1	11
1902	Germany	2	12
1903	Germany	3	13
1904	Germany	4	14
1905	Germany	5	15
1906	Germany	6	14
1907	Germany	7	13
1908	Germany	8	12
1909	Germany	9	11
1910	Germany	10	10
1911	Germany	11	9
1912	Germany	12	8
1913	Germany	13	7
1914	Germany	14	8
1915	Germany	15	9
1916	Germany	16	10
1917	Germany	17	11
1918	Germany	18	12
1919	Germany	19	13
1920	Germany	20	14

<tbody>
</tbody>

I'm only using the red text to indicate what was added; I don't need any help formatting the text, I know how to do that much.


Anyway, in other words I want to fill in the missing gaps as if that data increased/decreased linearly over that time period. I know how to calculate it manually, but I was wondering if there was a way to do it en masse automatically.

Any help will be appreciated.

 

[AlphaFrog]

This assumes X and Y are columns C:D

[color=darkblue]Sub[/color] Fill_XY()
    
    [color=darkblue]Dim[/color] col [color=darkblue]As[/color] Range, rngArea [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] lStart [color=darkblue]As[/color] [color=darkblue]Long[/color], lStop [color=darkblue]As[/color] [color=darkblue]Long[/color], lStep [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    Application.ScreenUpdating = [color=darkblue]False[/color]
    
    [color=darkblue]For[/color] [color=darkblue]Each[/color] col [color=darkblue]In[/color] Range("C:D").Columns
        [color=darkblue]For[/color] [color=darkblue]Each[/color] rngArea [color=darkblue]In[/color] col.SpecialCells(xlCellTypeBlanks).Areas
            lStart = rngArea(0).Value
            lStop = rngArea(rngArea.Count + 1)
            lStep = IIf(lStop > lStart, 1, -1)
            rngArea(1) = lStart + lStep
            [color=darkblue]If[/color] rngArea.Count > 1 [color=darkblue]Then[/color]
                rngArea.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=lStep, Stop:=lStop
            [color=darkblue]End[/color] [color=darkblue]If[/color]
        [color=darkblue]Next[/color]
    Next col
    
    Application.ScreenUpdating = [color=darkblue]True[/color]
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[JeremyBowyer]

Thanks very much Alpha.

I've been toying around with the macro you provided and it's not exactly what I need. It seems like what it does is adds or subtracts 1 for each cell going down the column. What I need is a way to bridge the gap between 2 values, if that makes sense.


For instance. If I use your macro in this modified example table, it doesn't work:

Year	Country	X	Y
1901	Germany	10	110
1902	Germany
1903	Germany
1904	Germany
1905	Germany		150
1906	Germany
1907	Germany
1908	Germany
1909	Germany
1910	Germany	100	100
1911	Germany
1912	Germany
1913	Germany		70
1914	Germany
1915	Germany
1916	Germany
1917	Germany
1918	Germany		120
1919	Germany
1920	Germany	200	140

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>

It produces this result:

Year	Country	X	Y
1901	Germany	10	110
1902	Germany	11	111
1903	Germany	12	112
1904	Germany	13	113
1905	Germany	14	150
1906	Germany	15	149
1907	Germany	16	148
1908	Germany	17	147
1909	Germany	18	146
1910	Germany	100	100
1911	Germany	101	99
1912	Germany	102	98
1913	Germany	103	70
1914	Germany	104	71
1915	Germany	105	72
1916	Germany	106	73
1917	Germany	107	74
1918	Germany	108	120
1919	Germany	109	121
1920	Germany	200	140

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>

What I'm looking for is a way to fill in those gaps based on both the starting point data and the end point data for each gap. Does that make sense?

 

[AlphaFrog]

It does make sense sort of. Given the data set in your first post, the gaps count was exactly the same amount as needed to be bridged. "Bridge the gaps" can be interpreted many ways. Do you want to round decimal values?


Try this one...

[color=darkblue]Sub[/color] Fill_XY()
    
    [color=darkblue]Dim[/color] col [color=darkblue]As[/color] Range, rngArea [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] lStart [color=darkblue]As[/color] [color=darkblue]Single[/color], lStop [color=darkblue]As[/color] [color=darkblue]Single[/color], lStep [color=darkblue]As[/color] [color=darkblue]Single[/color]
    
    Application.ScreenUpdating = [color=darkblue]False[/color]
    
    [color=darkblue]For[/color] [color=darkblue]Each[/color] col [color=darkblue]In[/color] Range("C:D").Columns
        [color=darkblue]For[/color] [color=darkblue]Each[/color] rngArea [color=darkblue]In[/color] col.SpecialCells(xlCellTypeBlanks).Areas
            [color=darkblue]Set[/color] rngArea = rngArea(0).Resize(rngArea.Count + 1)
            lStart = rngArea(1).Value
            lStop = rngArea(rngArea.Count + 1)
            lStep = (lStop - lStart) / rngArea.Count
            rngArea.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=lStep, Stop:=lStop
        [color=darkblue]Next[/color]
    Next col
    
    Application.ScreenUpdating = [color=darkblue]True[/color]
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]