VBA to interpolate between depths

brandon20

Board Regular
Joined
Feb 12, 2014
Messages
203
Hi,

I have two worksheets. Both have columns for (well name) and (Depth), one worksheet has a column for (pressure) and the other has a column for (Surface) that is associated with the well name and depth.
Simple enough.
What I need is to match up the surface from worksheet 2, with its associated pressure in worksheet 1.
The issue is that the pressure in worksheet 1 will occur at a depth in a BETWEEN the surfaces in worksheet 2.
I need the shallower (less depth) surface to populate the cell of this pressure.

It is all easier to see in this example:

Worksheet 1:
I filled in two (Surfaces) that would be populated if I had the code
WELLNAME
DEPTH
PRESSURE
SURFACE
RA-0074
8953
4185.7998
RA-0074
9000
4220
RA-0074
9028
4245
RA-0074
9110
4292
RA-0074
9165
4244.7002
RA-0074
9166
4244.6001
RA-0074
9182
4220.3999
RA-0074
9220
4238.1001
Z51CH
RA-0074
9350
4270.5
Z46CH
RA-0074
9447
4312
RA-0074
9539
4352
RA-0074
9570
4372.7998
RA-0074
9641
4539

<tbody>
</tbody>

Worksheet 2:
Wellname
DEPTH
Surface
RA-0074
9202.7
Z52_1
RA-0074
9204.9
Z51CH
RA-0074
9232.9
Z51
RA-0074
9237.3
Z48CH
RA-0074
9250.93
Z48
RA-0074
9313.2
Z46CH
RA-0074
9415.6
Z46
RA-0074
9421.4
Z44CH
RA-0074
9512.7
Z44

<tbody>
</tbody>

As you can see, Z51CH in worksheet 2 occurs at Depth 9204.9. The next surface in worksheet 2 occurs at depth 9232.9.
In worksheet 1, I filled in Z51CH at Depth 9220. This is because the Z51CH surface from worksheet 2 occurred at a depth 9204.9 and the NEXT surface (Z51) occurred at a depth 9232.9.
Worksheet 2, Z51CH surface is a range from 9204.9 to 9232.9.
Worksheet 1, pressure at depth 9220 falls BETWEEN worksheet 2 surface range 9204.9 to 9232.9
Therefore I need that pressure to be called Z51CH.


Any help would be greatly appreciated….
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,717
Office Version
  1. 365
Platform
  1. Windows
I cant help with VBA but this formula will do it. It will need adapting. I put your first table in A1:D14 and your lookup table in A17:C26 of the same worksheet. There may be easier formulas! Put this in D2:

=INDEX($C$18:$C$26,MATCH(IFERROR(LARGE($B$18:$B$26,COUNTIF($B$18:$B$26,">"&B2)+1),MIN($B$18:$B$26)),$B$18:$B$26,0))
 

brandon20

Board Regular
Joined
Feb 12, 2014
Messages
203
Hi,

Thank you so much! An equation that could do this would be much better for me as I am no expert with VBA, I can learn from an equation to adapt it to different problems in the future.

I think this one almost has it.

I noticed one issue,

When I ran the equation on multiple wells it seemed to come up with incorrect answers.
In column A I have 1000 different well names, RA-0001, RA-0100HST, RA-0500...... bla bla bla.

When I ran the equation on a single well it worked great. What would I throw into this equation to make it go through by specific well name first? I noticed it was not looking in column A.

Thanks again!

Brandon
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,300
Messages
5,836,489
Members
430,435
Latest member
Benforest1

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
Top