# Spreadsheet smart automated copying from one sheet to another.

The Original Data Sheet in the attached spreadsheet shows x, y coordinates and the corresponding true z values.
The Interpolation Sheet shows a different set of x, y coordinates (some overlapping), and the interpolated values imported from two different algorithms - bilinear and bicubic interpolation. There is also a column called z_truth.

My goal is to determine whether bilinear or bicubic interpolation is better.
It may be that for this initial data set that one sometimes does better than the other and vice versa.

To get a sense of how the interpolated data matched against the original data, I started copying rows from the Original Data Sheet to the Interpolation Sheet placing x, y coordinates from one near the other. If the coordinates match exactly, then there is only one copy. Otherwise, there are two copies where the original x, y coordinate falls between two x,y coordinates in interpolated sheet.

Currently, I am just going one row at a time from the Original Data Sheet and copying it to one or two locations in the Interpolation Sheet. This is taking too much time. I was wondering whether this copying could be automated.

If you could also help me with some metric to identify whether bilinear or bicubic interpolation is closer to the true values, I would appreciate that.

(I am now aware that my choice of original true value coordinates may not be so great; but this is a (good?) first start.

Thanks,
Paul
Compare_2d_Interpolations.xlsx
LVL 33
###### Who is Participating?

Commented:
The code below is in Module1 in attached workbook.

The principle for the transfer is to find original x and y values where the absolute distance to interpolation x and y values, is less or equal to 0.02.
The result is in columns K to O
The values you have found are moved to columns V to Z, so you can compare.

The limit 0.02 is set at the line "Const Limit As Single = 0.02".
That match what you have found.
You can change the value, and run again, it takes only a second.

The differences to z_truth are quite large for those where x an y don't match , so a compare to bilinear and bicubic might be meaningless.
If z_truth is the correct value (where does that come from), the interpolations can be compared to that, by dividing with z_truth to see the relative match for each.  Columns Q and R.
In row 1 is the average.
I would say the best is the one nearest to 1 (bicubic).
The same is done in columns S and T for the transferred values, but only where x and y does not match original x and y.

Try changing limit to 0.01, to only use values where x and y are closer to each other.
Then bilinear is best.

``````Option Explicit

Sub CompareInterpolation()
Dim wsOriginal As Worksheet, wsInterpol As Worksheet
Dim rwOriginal As Long, rwOriginalMax As Long
Dim rwInterpol As Long, rwInterpolMax As Long
Dim arOriginal() As Variant, arInterpolData() As Variant, arInterpolResult() As Variant
Const Limit As Single = 0.02

Set wsOriginal = Worksheets("Original Data")
Set wsInterpol = Worksheets("Intepolation")
rwOriginalMax = wsOriginal.Range("A1").CurrentRegion.Rows.Count
rwInterpolMax = wsInterpol.Range("A1").CurrentRegion.Rows.Count
arOriginal = wsOriginal.Range("A1:E" & rwOriginalMax)
arInterpolData = wsInterpol.Range("A1:B" & rwInterpolMax)
wsInterpol.Range("K2:O" & rwInterpolMax).ClearContents
arInterpolResult = wsInterpol.Range("K1:O" & rwInterpolMax)

For rwInterpol = 2 To rwInterpolMax
For rwOriginal = 2 To rwOriginalMax
If Abs(Round(arInterpolData(rwInterpol, 1), 3) - arOriginal(rwOriginal, 1)) <= Limit _
And Abs(Round(arInterpolData(rwInterpol, 2), 3) - arOriginal(rwOriginal, 2)) <= Limit Then
arInterpolResult(rwInterpol, 1) = arOriginal(rwOriginal, 1)
arInterpolResult(rwInterpol, 2) = arOriginal(rwOriginal, 2)
arInterpolResult(rwInterpol, 3) = arOriginal(rwOriginal, 3)
arInterpolResult(rwInterpol, 5) = arOriginal(rwOriginal, 5)
Exit For
End If
Next rwOriginal
Next rwInterpol
wsInterpol.Range("K1:O" & rwInterpolMax) = arInterpolResult
End Sub
``````
Compare_2d_Interpolations.xlsm
0

Author Commented:
@Ejgil Hedegaard,
Thanks for the reply. I got back very late, so I'll take a look at this tomorrow. Thanks again.
Paul
0

Author Commented:
>> If z_truth is the correct value (where does that come from),

Here is the function I used to generate the true values:
Z = 1 / ((2*(X-.4)+ .5*(Y-.2) -.3)^2 + .01) + 1/ ((X/3 + 3*Y -.9)^2 + .04) - 6;
It is a variation of humps which is a 1d function.
http://bioinformatics.intec.ugent.be/MotifSuite/INCLUSive_for_users/CPU_64/Matlab_Compiler_Runtime/v79/toolbox/matlab/demos/html/funfuns.html#1
0

Author Commented:
I was working at home today and my laptop finally died from the beating out took  when the new  Windows 10 update came by last week and did  havoc to my visual  studio 2015 community edition when trying to build the gnu scientific library, which is what I used for the interpolation. Literally it took two days of constant disk activity to repair and  install  components due to the update.

But I could see your spreadsheet on my kindle tablet. You did an excellent job. Thank you so much. I don't know the language so I'll ask a different question to get an explanation.  I couldn't change the limit to 0.01 on the tablet or on my laptop using libre office calc. But tomorrow I'll be at the office and I will use excel.

I was thinking of adding a standard deviation into the mix as well as the mean that you have already done.

I would appreciate instructions on how to access your code in excel and how to run it. Thanks again for your help.
0

Author Commented:
By rights I should have included some 3d pictures of the surface. It has multiple local maximums with different curvatures. By rights I think I should be careful to select original coordinates to be close to those max locations.

Also most of the region varies very smoothly. Those regions should be weighted less than where the multiple humps are located. I'll try to do that after I get another computer and get everything installed again. I may be able to find a workstation at work, but at the moment the ones I need are being used. I purposely chose the function to have some hard to interpolate regions as well as relatively large smooth regions.
0

Author Commented:
0

Commented:
To edit the VBA code press Alt+F11, and in the project explorer at the left, double click on the module if it does not open with the module active.

To run it, press F5, or the button that looks like a tape recorder Play button.
Or go to the developer tab, select macros, and run from there.
Or a button could be place on the sheet.

Standard deviation will also tell something about how good the match is.
But the differences are large, so in my opinion none of the interpolations match.
0

Author Commented:
Many thanks for your program and your analysis. I will be able to use this with better choice of sampled coordinates and do a better comparison. Thanks again for saving me many hours of tedious, error prone copying.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.