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:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Year[/TD]
[TD]Country[/TD]
[TD]X[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]1901[/TD]
[TD]Germany[/TD]
[TD]1[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]1902[/TD]
[TD]Germany[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1903[/TD]
[TD]Germany[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1904[/TD]
[TD]Germany[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1905[/TD]
[TD]Germany[/TD]
[TD][/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]1906[/TD]
[TD]Germany[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1907[/TD]
[TD]Germany[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1908[/TD]
[TD]Germany[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1909[/TD]
[TD]Germany[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1910[/TD]
[TD]Germany[/TD]
[TD]10[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]1911[/TD]
[TD]Germany[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1912[/TD]
[TD]Germany[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1913[/TD]
[TD]Germany[/TD]
[TD][/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]1914[/TD]
[TD]Germany[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1915[/TD]
[TD]Germany[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1916[/TD]
[TD]Germany[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1917[/TD]
[TD]Germany[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1918[/TD]
[TD]Germany[/TD]
[TD][/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]1919[/TD]
[TD]Germany[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1920[/TD]
[TD]Germany[/TD]
[TD]20[/TD]
[TD]14[/TD]
[/TR]
</tbody>[/TABLE]


I want it to look like this:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Year[/TD]
[TD]Country[/TD]
[TD]X[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]1901[/TD]
[TD]Germany[/TD]
[TD]1[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]1902[/TD]
[TD]Germany[/TD]
[TD]2[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]1903[/TD]
[TD]Germany[/TD]
[TD]3[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]1904[/TD]
[TD]Germany[/TD]
[TD]4[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]1905[/TD]
[TD]Germany[/TD]
[TD]5[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]1906[/TD]
[TD]Germany[/TD]
[TD]6[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]1907[/TD]
[TD]Germany[/TD]
[TD]7[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]1908[/TD]
[TD]Germany[/TD]
[TD]8[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]1909[/TD]
[TD]Germany[/TD]
[TD]9[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]1910[/TD]
[TD]Germany[/TD]
[TD]10[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]1911[/TD]
[TD]Germany[/TD]
[TD]11[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]1912[/TD]
[TD]Germany[/TD]
[TD]12[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]1913[/TD]
[TD]Germany[/TD]
[TD]13[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]1914[/TD]
[TD]Germany[/TD]
[TD]14[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]1915[/TD]
[TD]Germany[/TD]
[TD]15[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]1916[/TD]
[TD]Germany[/TD]
[TD]16[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]1917[/TD]
[TD]Germany[/TD]
[TD]17[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]1918[/TD]
[TD]Germany[/TD]
[TD]18[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]1919[/TD]
[TD]Germany[/TD]
[TD]19[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]1920[/TD]
[TD]Germany[/TD]
[TD]20[/TD]
[TD]14[/TD]
[/TR]
</tbody>[/TABLE]


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:

[TABLE="class: grid, width: 500"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Year[/TD]
[TD="width: 64"]Country[/TD]
[TD="width: 64"]X[/TD]
[TD="width: 64"]Y[/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]1901[/TD]
[TD="width: 64"]Germany[/TD]
[TD="width: 64, align: right"]10[/TD]
[TD="width: 64, align: right"]110[/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]1902[/TD]
[TD="width: 64"]Germany[/TD]
[TD="width: 64"] [/TD]
[TD="width: 64"] [/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]1903[/TD]
[TD="width: 64"]Germany[/TD]
[TD="width: 64"] [/TD]
[TD="width: 64"] [/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]1904[/TD]
[TD="width: 64"]Germany[/TD]
[TD="width: 64"] [/TD]
[TD="width: 64"] [/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]1905[/TD]
[TD="width: 64"]Germany[/TD]
[TD="width: 64"] [/TD]
[TD="width: 64, align: right"]150[/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]1906[/TD]
[TD="width: 64"]Germany[/TD]
[TD="width: 64"] [/TD]
[TD="width: 64"] [/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]1907[/TD]
[TD="width: 64"]Germany[/TD]
[TD="width: 64"] [/TD]
[TD="width: 64"] [/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]1908[/TD]
[TD="width: 64"]Germany[/TD]
[TD="width: 64"] [/TD]
[TD="width: 64"] [/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]1909[/TD]
[TD="width: 64"]Germany[/TD]
[TD="width: 64"] [/TD]
[TD="width: 64"] [/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]1910[/TD]
[TD="width: 64"]Germany[/TD]
[TD="width: 64, align: right"]100[/TD]
[TD="width: 64, align: right"]100[/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]1911[/TD]
[TD="width: 64"]Germany[/TD]
[TD="width: 64"] [/TD]
[TD="width: 64"] [/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]1912[/TD]
[TD="width: 64"]Germany[/TD]
[TD="width: 64"] [/TD]
[TD="width: 64"] [/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]1913[/TD]
[TD="width: 64"]Germany[/TD]
[TD="width: 64"] [/TD]
[TD="width: 64, align: right"]70[/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]1914[/TD]
[TD="width: 64"]Germany[/TD]
[TD="width: 64"] [/TD]
[TD="width: 64"] [/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]1915[/TD]
[TD="width: 64"]Germany[/TD]
[TD="width: 64"] [/TD]
[TD="width: 64"] [/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]1916[/TD]
[TD="width: 64"]Germany[/TD]
[TD="width: 64"] [/TD]
[TD="width: 64"] [/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]1917[/TD]
[TD="width: 64"]Germany[/TD]
[TD="width: 64"] [/TD]
[TD="width: 64"] [/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]1918[/TD]
[TD="width: 64"]Germany[/TD]
[TD="width: 64"] [/TD]
[TD="width: 64, align: right"]120[/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]1919[/TD]
[TD="width: 64"]Germany[/TD]
[TD="width: 64"] [/TD]
[TD="width: 64"] [/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]1920[/TD]
[TD="width: 64"]Germany[/TD]
[TD="width: 64, align: right"]200[/TD]
[TD="width: 64, align: right"]140[/TD]
[/TR]
</tbody>[/TABLE]




It produces this result:

[TABLE="class: grid, width: 500"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Year[/TD]
[TD="width: 64"]Country[/TD]
[TD="width: 64"]X[/TD]
[TD="width: 64"]Y[/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]1901[/TD]
[TD="width: 64"]Germany[/TD]
[TD="width: 64, align: right"]10[/TD]
[TD="width: 64, align: right"]110[/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]1902[/TD]
[TD="width: 64"]Germany[/TD]
[TD="width: 64, align: right"]11[/TD]
[TD="width: 64, align: right"]111[/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]1903[/TD]
[TD="width: 64"]Germany[/TD]
[TD="width: 64, align: right"]12[/TD]
[TD="width: 64, align: right"]112[/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]1904[/TD]
[TD="width: 64"]Germany[/TD]
[TD="width: 64, align: right"]13[/TD]
[TD="width: 64, align: right"]113[/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]1905[/TD]
[TD="width: 64"]Germany[/TD]
[TD="width: 64, align: right"]14[/TD]
[TD="width: 64, align: right"]150[/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]1906[/TD]
[TD="width: 64"]Germany[/TD]
[TD="width: 64, align: right"]15[/TD]
[TD="width: 64, align: right"]149[/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]1907[/TD]
[TD="width: 64"]Germany[/TD]
[TD="width: 64, align: right"]16[/TD]
[TD="width: 64, align: right"]148[/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]1908[/TD]
[TD="width: 64"]Germany[/TD]
[TD="width: 64, align: right"]17[/TD]
[TD="width: 64, align: right"]147[/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]1909[/TD]
[TD="width: 64"]Germany[/TD]
[TD="width: 64, align: right"]18[/TD]
[TD="width: 64, align: right"]146[/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]1910[/TD]
[TD="width: 64"]Germany[/TD]
[TD="width: 64, align: right"]100[/TD]
[TD="width: 64, align: right"]100[/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]1911[/TD]
[TD="width: 64"]Germany[/TD]
[TD="width: 64, align: right"]101[/TD]
[TD="width: 64, align: right"]99[/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]1912[/TD]
[TD="width: 64"]Germany[/TD]
[TD="width: 64, align: right"]102[/TD]
[TD="width: 64, align: right"]98[/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]1913[/TD]
[TD="width: 64"]Germany[/TD]
[TD="width: 64, align: right"]103[/TD]
[TD="width: 64, align: right"]70[/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]1914[/TD]
[TD="width: 64"]Germany[/TD]
[TD="width: 64, align: right"]104[/TD]
[TD="width: 64, align: right"]71[/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]1915[/TD]
[TD="width: 64"]Germany[/TD]
[TD="width: 64, align: right"]105[/TD]
[TD="width: 64, align: right"]72[/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]1916[/TD]
[TD="width: 64"]Germany[/TD]
[TD="width: 64, align: right"]106[/TD]
[TD="width: 64, align: right"]73[/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]1917[/TD]
[TD="width: 64"]Germany[/TD]
[TD="width: 64, align: right"]107[/TD]
[TD="width: 64, align: right"]74[/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]1918[/TD]
[TD="width: 64"]Germany[/TD]
[TD="width: 64, align: right"]108[/TD]
[TD="width: 64, align: right"]120[/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]1919[/TD]
[TD="width: 64"]Germany[/TD]
[TD="width: 64, align: right"]109[/TD]
[TD="width: 64, align: right"]121[/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]1920[/TD]
[TD="width: 64"]Germany[/TD]
[TD="width: 64, align: right"]200[/TD]
[TD="width: 64, align: right"]140[/TD]
[/TR]
</tbody>[/TABLE]



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]