Hi guys, Chikaleen here, have I got a puzzle for you!
I find and map water in the oilfields for a living. We then secure, permit, and transport the water via temporary rubber hose to the location it needs to go to. This keeps thousands of tanker truck trips off the county roads and keep them from kicking up dust every time they pass your house. I'm attempting to make my methods more automated: The old-school guys like to get in a pickup, drive 2+ hours to a rig area, and proceed to drive in circles to find useful water for the frac jobs that take place after the rig moves out. What used to take weeks to do driving around the countryside now takes minutes thanks to this fancy operation I've got going on to make my job easier. I'm asking for your help in making it simpler yet - find a way to mesh these two spreadsheets together to pinpoint exact distances from my source water to the job I need to get it at. Bear with me and I'll explain and maybe teach you some of the more fun aspects of this job.
Legal land descriptions operate on a grid called the Public Land Survey System (PLSS) and are divided into "Township" and "Range" grids (horizontal and vertical lines, respectively) and then further subdivided into 36 "Sections" of that grid. The 6x6 grid is commonly referred to as just a "Township." With the previous help of some Mr. Excel members (because you guys are the greatest) I have gotten the sheet to recognize a data point (NWNES34T150NR97W, or whatever) and plot it to a map I have drawn.
I have two sheets I'm proud as all heck of that I operate out of for this task. ALL RIGS 'Data' tab can be updated daily by a simple copy/paste from the state website. Make sure the VLOOKUP is matching all of the colors, make sure formulas are selecting the correct cells, and then run the ClearCounty and Main macros on the 'Map' tab and voila! All of the active drilling rigs in the area are updated to within a <1 mile radius of their actual location (which is close enough for what I need.)
View ALL RIGS 8.20.14 here https://www.dropbox.com/s/gjbzj4m7c1m68ug/ALL RIGS 8.20.14.xlsm?dl=0
The second sheet is made up of fixed data points of ponds, streams, rivers, water wells, etc. that I have permitted or access to for utilization in the fracking process. I have removed a lot of personal data and ownership information from this sheet for the purpose of this exercise, so normally there is a lot more in there to sort by and play with.
View Sources 8.20.14 here https://www.dropbox.com/s/hot8qgxyx56vibu/Sources 8.20.14.xlsm?dl=0
NOW TO THE FUN PART
I want to be able to link or overlay these sheets somehow to be able to see which Operator I need to go talk to about providing the closest and easiest accessible water for. Here's the steps in my head of how to get there:
Step 0: (sort of backwards step) If you can suggest a better way of using VBE on the current sheets, I'm all for it.
Step 1: Make the Sources recognize the Rig locations in relation to their static points.
Step 2: Make the Rigs tell you the distance in cells (one square mile) it is away from a source using the Pythagorean Theorem (A^2+B^2=C^2)
Step 3: Make the Sources only return Rig information in a X cell radius of the source.
Step 4: Return a printable or viewable Rig sheet of X Source within X radius.
Step 5: Whatever else could come next, I have no idea.
Thanks in advance to anyone that tries it out. If I don't get many bites to this article I'll attempt to use interesting land or water tidbits for my thread bumps to keep you all entertained. I'm sitting right here if you have any questions as to how or why something was set up in a particular way, and thanks again
Chikaleen
I find and map water in the oilfields for a living. We then secure, permit, and transport the water via temporary rubber hose to the location it needs to go to. This keeps thousands of tanker truck trips off the county roads and keep them from kicking up dust every time they pass your house. I'm attempting to make my methods more automated: The old-school guys like to get in a pickup, drive 2+ hours to a rig area, and proceed to drive in circles to find useful water for the frac jobs that take place after the rig moves out. What used to take weeks to do driving around the countryside now takes minutes thanks to this fancy operation I've got going on to make my job easier. I'm asking for your help in making it simpler yet - find a way to mesh these two spreadsheets together to pinpoint exact distances from my source water to the job I need to get it at. Bear with me and I'll explain and maybe teach you some of the more fun aspects of this job.
Legal land descriptions operate on a grid called the Public Land Survey System (PLSS) and are divided into "Township" and "Range" grids (horizontal and vertical lines, respectively) and then further subdivided into 36 "Sections" of that grid. The 6x6 grid is commonly referred to as just a "Township." With the previous help of some Mr. Excel members (because you guys are the greatest) I have gotten the sheet to recognize a data point (NWNES34T150NR97W, or whatever) and plot it to a map I have drawn.
I have two sheets I'm proud as all heck of that I operate out of for this task. ALL RIGS 'Data' tab can be updated daily by a simple copy/paste from the state website. Make sure the VLOOKUP is matching all of the colors, make sure formulas are selecting the correct cells, and then run the ClearCounty and Main macros on the 'Map' tab and voila! All of the active drilling rigs in the area are updated to within a <1 mile radius of their actual location (which is close enough for what I need.)
View ALL RIGS 8.20.14 here https://www.dropbox.com/s/gjbzj4m7c1m68ug/ALL RIGS 8.20.14.xlsm?dl=0
The second sheet is made up of fixed data points of ponds, streams, rivers, water wells, etc. that I have permitted or access to for utilization in the fracking process. I have removed a lot of personal data and ownership information from this sheet for the purpose of this exercise, so normally there is a lot more in there to sort by and play with.
View Sources 8.20.14 here https://www.dropbox.com/s/hot8qgxyx56vibu/Sources 8.20.14.xlsm?dl=0
NOW TO THE FUN PART
I want to be able to link or overlay these sheets somehow to be able to see which Operator I need to go talk to about providing the closest and easiest accessible water for. Here's the steps in my head of how to get there:
Step 0: (sort of backwards step) If you can suggest a better way of using VBE on the current sheets, I'm all for it.
Step 1: Make the Sources recognize the Rig locations in relation to their static points.
Step 2: Make the Rigs tell you the distance in cells (one square mile) it is away from a source using the Pythagorean Theorem (A^2+B^2=C^2)
Step 3: Make the Sources only return Rig information in a X cell radius of the source.
Step 4: Return a printable or viewable Rig sheet of X Source within X radius.
Step 5: Whatever else could come next, I have no idea.
Thanks in advance to anyone that tries it out. If I don't get many bites to this article I'll attempt to use interesting land or water tidbits for my thread bumps to keep you all entertained. I'm sitting right here if you have any questions as to how or why something was set up in a particular way, and thanks again
Chikaleen
