How to compare different size datasets with varying time increments

[ThermalStress]

Hi Chandooers,


My worksheet does this; I have a workbook which simulates the temperatures and displacements across hundreds of test points within a 2D model of an aircraft engine, at discrete time increments from the start of a flight cycle to the end. It's used for analysis and engineering design.


This involves multiple worksheets each with several large tables (e.g. about 301x250 or so, as different subassemblies seem to have different tables). In the first column of each table is time so rows correspond to each point in time, and in subsequent columns is an x or y or temp value for one of these test points.



Say I want to see the relative movements between two chosen points (A_x, Ay) and (B_x, B_y), at numerous discrete points in a flight cycle from say 0 to 12,000 seconds, I'd do this by calculating a column of (B_x - A_x) and (B_y - A_y).


The problem is this; We want to compare pairs of points which are close together in real life (from this, you could see the maximum changes in clearance, or temperature differentials at each stage in the flight). However we can't directly compare between tables in Excel as the pairs which fall in different tables will have a different number of rows. The analysis software spat time series data which seems to have different numbers of divisions, not necessarily a consistent time-step (because flights are broken into stages with different required resolution). Some tables have about 150 rows, some have about 350 for example.


Is there a way of tying up different tables (e.g. normalising Table A to have the same number of rows as Table B by automatically interpolating between the closest rows) so that comparisons can be made?


We can say that all displacements and temperatures are continuous functions with time, and we can say that all data sets start from 0 seconds and end at 12000 seconds, so theoretically I think I could do this with curve-fitting with a graph, getting coefficients from the polynomial equation and producing new points at the required time intervals (say just every 5 seconds/or map it onto Table 2 time points). But there must be a better way. Is there a formula method (preferred) or VBA to do this?


Tl;Dr How to normalise one column or table of data with varying time intervals to compare points with another column or table of data with different size and different time intervals.


e.g.
Sheet 1
Table 1
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Time[/TD]
[TD]DATAT1P1x[/TD]
[TD]DATAT1P1y [/TD]
[TD]DATAT1P150x [/TD]
[TD]DATAT1P150y[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0.001[/TD]
[TD]-0.004[/TD]
[TD]0.020[/TD]
[TD]-0.020[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]0.254[/TD]
[TD]0.353[/TD]
[TD]0.053[/TD]
[TD]-0.050[/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12000[/TD]
[TD]1.241[/TD]
[TD]-0.664[/TD]
[TD]1.421[/TD]
[TD]2.104[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Time[/TD]
[TD]DATAT2P151x[/TD]
[TD]DATAT2P151y[/TD]
[TD]DATAT2P350x[/TD]
[TD]DATAT2P350y[/TD]
[TD]DATAT2P351x[/TD]
[TD]DATA2P351y[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3.7[/TD]
[TD][/TD]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5.2[/TD]
[TD][/TD]
[TD][/TD]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]...[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]...[/TD]
[/TR]
</tbody>[/TABLE]

This involves multiple worksheets each with several large tables (e.g. about 301x250 or so). In the first column of each table is time so rows correspond to each point in time, and in subsequent columns is an x or y or temp value for one of these test points.