Extracting selected rows from multiple columns(40 posts)
I'll send you the file with what I've done so far but will present the results here in case others are interested and especially Enthusiast.
I've run (the 2nd version) of my VBA against your data (~17,500 bore/layer lines ) which resulted in the extraction of the Dolerite layers from each Bore (~1000 Dolerite lines). The extraction is as per your requirements ....
What I am hoping for is some way to "split out" the Dolerite layers for each hole so that I can get:
a) Order of the Dolerite (to define D01, D02 etc.)
b) Depth from and to (to also calculate the thickness from this)
Here is a sample of the result .....
The second part of your requirement isn't clear as yet. We know that you want to match the Dolerite with the data in the Elevation worksheet.
My next step would then be to link the surface elevation of the borehole minus the depth from (and to) to calculate the Top-of-Dolerite elevation and the Bottom-of-Dolerite evelation. This will help me to correlate the dolerite across my area.
What is not clear is how you want to do that. I've included in the file an example of a correlation between the 2 sheets assuming you need to see only total Dolerite information for each elevation. It should be noted that this was done using native Excel functions - the VBA results weren't necessary.
I think that to complete this you'll need to mock up the results you are looking for in the file and post a screenshot and send the file to Enthusiast and me.
Here is the VBA with a few minor details changed - I tightened up on the code a bit in anticipation of Enthusiast's review and added more comments.
Sub IsolateLith() Application.ScreenUpdating = False 'set to True for debugging/False to improve performance Sheets("Lithology").Activate 'Make sure we are in the correct spreadsheet Sample = "dolerite" 'Change this to the Lithology for analysis NSamples = Range("A1").End(xlDown).Row 'number of samples Range(Cells(1, 7), Cells(NSamples, 12)).ClearContents 'Clear out existing results rows 'Sets Headers Range("G1") = "Bore" Range("H1") = "Depth1" Range("I1") = "Depth2" Range("J1") = "Lithology" Range("K1") = "Thickness" Range("L1") = "DOL#" TRow = 2 'The first Target Row for result For i = 1 To NSamples 'Will cycle through each Bore row If UCase(Trim(Cells(i, 4))) = UCase(Trim(Sample)) Then 'when the Bore contains "Dolerite" Range(Cells(i, 1), Cells(i, 4)).Copy 'Copies the information from the original data Cells(TRow, 7).PasteSpecial 'Pastes it to the Dolerite resuts row Cells(TRow, 11) = Cells(TRow, 9) - Cells(TRow, 8) 'Calculates thickness If Cells(TRow, 7) <> Cells(TRow - 1, 7) Then 'If this is the 1st instance of Dolerite in the Bore Cells(TRow, 12) = 1 Else 'If more than 1 Dolerite in a Bore Cells(TRow, 12) = Cells(TRow - 1, 12) + 1 'Determine the order of Dolerite in the Bore End If TRow = TRow + 1 'go to the next results row End If Next End Sub
I am still in total awe of what you can do with macros!
Ok - Firstly, I like what you have done with the Elevation tab - that is great!
Secondly, I have added 2 columns (M & N) in the Lithology tab - a Top-of-Dolerite elevation and Bottom-of-Dolerite elevation. What I am hoping for is that there will be a general range or better still a "grouping" of elevations so that I can use this to correlate across the area.
To explain it so that you can visualise it - the boreholes are all drilled from different surface elevations hence the depths aren't very useful to visualise or imagine the surface of the dolerite BUT if we can convert that to an elevation (above sea level) then we can start to visualise the top of that surface (or bottom or both) because we can group where these elevations are similar or within a reasonable range.
Hope this helps - thanks for your help again!
P.S. How do you guys post the screenshots here?
Can you send back the file with some of the values you want filled in (explaining which cell or what calculation you used to derive those values) and then it should be relatively easy to complete the macro.
I've done some additional work on the macro - the main improvement is to place the results in a new worksheet. That way, if you want to do the same analysis on another lithology it will be easier. When you send your file I'll incorporate the new macro.
I sent the file to you about an hour ago - I used the elevations from the Elevation tab and subtracted the "From" and "To" depths to give a top-of-dolerite elevation and a bottom-of-dolerite elevation in the Lithology tab (column M & N). I think you are right - it would be best to place the results in a new worksheet.
Does this help to clarify - if not please give me a shout?
Hi again moreeg - I have to go to the field for the next couple of days but will be back on Monday. I will try to look through the file during that time but there is very sporadic internet access (so cant reply or post). Can I send and post a full reply on Monday or Teusday latest if that is ok?
No worries, take all the time you need. In the meantime I've been meaning to post the results of our off-line conversations to keep others that are interested up to date but haven't had a chance. Will do that in the next couple of days.
Take care in the field ... Don't fall into any holes
For those that have been following this here is the progress;
Part II of the problem required Eligeol to spell it out for me in Excel terms - the geological explanation was beyond me. In the end there were to new columns required;
TOD Elev .... which is the total elevation for a Bore which is listed in the Elevation spreadsheet minus the beginnig depth of a Dolerite layer
BOD Elev .... is the total elevation minus the ending depth of a Dolerite layer
The beginning and ending depths of Dolerite were extracted by the Macro. The 2 new columns were easily calculated using vlookup that matched the Bore name on the Dolerite Spreadsheet with the Bore name on the Elevation spreadsheet and extracted the Bore elevation for the particular Dolerite layer.
There was really no need to do this in a macro but I added it into the Macro nonetheless on the theory that if the same exercise was to be repeated for another lithography this macro approach might be simpler (I'm not totally convinced either).
Here is the macro that was used. As indicated above, I altered it so that the results were deposited into a new spreadsheet.
Sub IsolateLith() Sample = "dolerite" 'Change this to the Lithology you want to analyse Sheets(Sample).Activate 'Create a sheet with the name of the sample you are analysing NSamples = Sheets("Lithology").Range("A1").End(xlDown).Row 'number of samples Range(Cells(1, 1), Cells(NSamples, 20)).ClearContents 'Clear out existing results rows Application.ScreenUpdating = False 'set to True for debugging/False to improve performance 'Sets Headers Range("A1") = "Bore" Range("B1") = "Depth1" Range("C1") = "Depth2" Range("D1") = "Lithology" Range("E1") = "Thickness" Range("F1") = "DOL#" TRow = 2 'The first Target Row for results For i = 1 To NSamples 'Will cycle through each Bore row in Lithology sheet If UCase(Trim(Sheets("Lithology").Cells(i, 4))) = UCase(Trim(Sample)) Then 'when the Bore contains "Dolerite...." Range(Sheets("Lithology").Cells(i, 1), Sheets("Lithology").Cells(i, 4)).Copy 'Copies the information from the original data and ... Cells(TRow, 1).PasteSpecial 'Pastes it to the Dolerite results Target Row then ... Cells(TRow, 5) = Cells(TRow, 3) - Cells(TRow, 2) 'Calculates thickness If Cells(TRow, 1) <> Cells(TRow - 1, 1) Then 'If this is the 1st instance of Dolerite in the Bore Cells(TRow, 6) = 1 'Sets the Dolerite layer number as '1' Else 'If more than 1 Dolerite in a Bore Cells(TRow, 6) = Cells(TRow - 1, 6) + 1 'Determine the Layer number of Dolerite in the Bore End If TRow = TRow + 1 'go to the next target row End If Next NDol = Range("A1").End(xlDown).Row 'Number of resulting Dolerite samples Range("H1") = "TOD Elev" Range("H2").Formula = "=IFERROR(VLOOKUP(A2,Elevation!$A$1:$B$610,2,FALSE),"""")-B2" Range("I1") = "BOD Elev" Range("I2").Formula = "=IFERROR(VLOOKUP(A2,Elevation!$A$1:$B$610,2,FALSE),"""")-C2" Range("H2:I2").Copy Range("H2:I" & NDol).PasteSpecial Application.CutCopyMode = False Range("A1").Select End Sub
You can see near the end of the macro where I just plonked in the vlookup formula (rather than creating a VBA algorithm to do the same thing)
Among other improvement I would make to this are;
- Prompting for the lithology
Providing a listing of Lithologies from which to select
Automatically creating the new spreadsheet (or checking for its existence before proceeding)
This is a sample of the resulting spreadsheet
Here is a sample of the Elevation spreadsheet. Columns C and D were calculated using vlookups to the Dolerite spreadsheet. They are not included in the Macro.
The Dolerite spreadsheet containing the results of the Macro has over 1000 rows of data which will still be difficult to analyse for conclusions about the whole bore digging enterprise so Eligeol promises that at some point there will be a Part III to this saga.
Hi again and apologies for the late follow-up,
I have been roped into a whole bunch of non drilling related activities so to be honest havent had a chance to go through it fully. I will however be looking through all my data including how to handle this dolerite problem over the Christmas period (yeah - I know it sounds sad but if thats what it takes for the project to be successful then so be it!).
I really like the way the results are generated - I didnt quite expect that - so thanks also for that moreeg! This I hope will also help me to tie/correlate the dolerite layers across the area i.e. Dolerite layer 1 in one hole might be the equivalent of Dolerite layer 3 in the next closest hole...
I have no doubt that there will be a Part III to this saga as it seems to evolve into another monster as I get within reach of a semblance of an answer - certainly not as simple as I imagined it would be!
Thanks again for your help and I wish all a Merry Christmas and Happy New Year.
Hi moreeg, Enthusiast and all that have followed this post.
I worked through the data using your macro and this is what I've managed to produce thus far - see attached pic (I hope I got it right since this is my first pic posting!). The Dolerite issue is still a continual work in progress but at least I am getting closer to understanding the spatial aspects of it - the saga will more-than-likely continue :)
Thanks again for all your help and patience!