Automatically extrapolating data?

JeremyBowyer

New Member
Joined
Jan 20, 2014
Messages
2
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. :cool:


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.
 
This assumes X and Y are columns C:D

Code:
[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]
 
Upvote 0
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?
 
Upvote 0
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...

Code:
[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]
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top