Multiple Linear Interpolations on a Dynamic Range

[HideAndSeekChampion]

Hello all,

I have a VBA problem that I hope (and given the quality of some of the posts I have read, am quite sure) is solvable.

I need to perform a linear interpolation using a 3 column range of cells, where the first column is an identifier for different data sets (see example below).

The process needs to be dynamic, so that the number of data sets (each group of ID's) can change each time the VBA is run, and the end result is to be a table as described in the example below, containing interpolated Y values for a standard 1D array of X values.

E.G.

Input/Starting point:

ID, X, Y
A, 1, 10
A, 3, 30
A, 4, 40
B, 1, 10
B, 2, 20
B, 3, 30


Result (Standardised range of x values along the top, ID's down the left)
1, 2, 3, 4
A 10,20,30,40
B 10,20,30,40


I have VBA function to perform the Linear Interpolations for a given range of X's and Y's, and a desired X, however this is not automated nor dynamic (would need to manually adjust formulas, and create table each time).

Any advice would be greatly appreciated. If further information is required, please le me know.

Cheers.