Find first and last values based on one value

Wes

Board Regular
Joined
Jan 30, 2004
Messages
194
First I appologize if this has already been posted.

I need to find the First value and the last value of a range. A value is put into cell C12: number 1 for example and what happens is that excel will go down range H2:H9 and find the First Depth - "0" in this case and the last
Depth 100.
Daily Reporting Ver 1.0.3.xls
EFGHI
1CodeActivityBHADepth
21Rigup/TearDown10
37RigService10
42Drilling1100
56Trips2100
615PressureTest2100
74Coring2100
82Drilling2200
97RigService2200
10
11
12BHAValue1
13MinDepth
14MaxDepth
15
DailyReports


All help appreciated.
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Is G2:H9 is always sorted on G (in ascending order) as your exhibit appears to suggest?
 

Wes

Board Regular
Joined
Jan 30, 2004
Messages
194
For the most part. On the rare occasion the depth will become less and then increase.
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
see the example:
Book1
ABCD
1KeyValue
210
3110
410
52500
625
722
8310
921
10315
11
12MaxMin
131100
1425001
1531510
Sheet1


the formulas are of the form:

=MAX(IF($A$2:$A$10=A13,$B$2:$B$10))

& need to be array enterred using control + shift + enter, not just enter
 

Wes

Board Regular
Joined
Jan 30, 2004
Messages
194

ADVERTISEMENT

Thanks PaddyD, that works great for finding min and max :pray: I'm off to the races (y)
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
you're welcome. fwiw, this can also be done using a pivot table - an easier option in my view.
 

Wes

Board Regular
Joined
Jan 30, 2004
Messages
194

ADVERTISEMENT

I'd actually like to do it in VBA any ideas?
 

fwguam

New Member
Joined
Feb 18, 2004
Messages
1
"need to be array enterred using control + shift + enter" What is array enter?

Thanks,
fwguam
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
wes,

describe what you want the vba to do in a little more detail.

fwguam - welcome to the board! review the help file for "About array formulas and how to enter them" - post back if it's still not clear.
 

Wes

Board Regular
Joined
Jan 30, 2004
Messages
194
I would like the VBA code to find the last value and the first value of a range on a spreadsheet that is determine by one value. The value would be where the BHA# 1 started and where the BHA #1 ended. These values would also be found for BHA #2. Which we have done on the spreadsheet and I'm just repeting myself because I need the typing skills ... really. :wink:

Now, a userform would be set up with a text box, for the BHA number (txBhaNum) and text boxes ( txStart & txEnd) for the start and end depths. Thie BHA number/value would be controlled by the user. As the user changed the BHA value the first and the last depth values would be found and displayed in threir own text boxes on the userform.

So what is needed is code to place in the userform that refrences a range in a spreadsheet (DailyReports) and shows the values of end depth and start depth ( txStart & txEnd) on the userform all controlled by the BHA number (txBhaNum) on the userform.

I hope this helps a bit. :biggrin:
 

Watch MrExcel Video

Forum statistics

Threads
1,123,385
Messages
5,601,329
Members
414,445
Latest member
walramgo02

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