SEARCH

The How-To Geek Forums Have Migrated to Discourse

How-To Geek Forums / Microsoft Office

Extracting selected rows from multiple columns

(40 posts)
  • Started 2 years ago by Eligeol
  • Latest reply from Eligeol
  • Topic Viewed 3152 times

Eligeol
Posts: 31

Tks to all :)

Posted 2 years ago
Top
 
Lighthouse
Posts: 13598

Is it solved ?

Posted 2 years ago
Top
 
Eligeol
Posts: 31

As far as I know not yet...will post reply when it is if thats ok?

Posted 2 years ago
Top
 
Lighthouse
Posts: 13598

That's fine :)

Posted 2 years ago
Top
 
Enthusiast
Posts: 566

No, not solved. I just received the email. Thank you Eligeol.

Posted 2 years ago
Top
 
moreeg
Posts: 842

Hi Eligeol

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

.
.

Posted 2 years ago
Top
 
Enthusiast
Posts: 566

Sorry I haven't had the time yet to spend with this, as I am behind a deadline at the moment. I will make time to look it over on the weekend.

@moreeg,

I hardly meant to "daunt" you into radically revising your code, although it does appear to have had a reducing effect :)

Posted 2 years ago
Top
 
moreeg
Posts: 842

@ Enthusiast
Daunting is good
Daunting is incentive to do better
Daunt away.

Posted 2 years ago
Top
 
Eligeol
Posts: 31

Hi again,

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?

Posted 2 years ago
Top
 
Scott
Posts: 5618

One of many ways,
http://www.howtogeek.com/forum.....ost-119615

Posted 2 years ago
Top
 
moreeg
Posts: 842

Hi Eligeol

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.

Posted 2 years ago
Top
 
Eligeol
Posts: 31

Hi moreeg,

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?

Thanks again.

Posted 2 years ago
Top
 
Eligeol
Posts: 31

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?

Thanks again.

Posted 2 years ago
Top
 
moreeg
Posts: 842

Hi Eligeol
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

Posted 2 years ago
Top
 
moreeg
Posts: 842

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.

Posted 2 years ago
Top
 
Eligeol
Posts: 31

Hi All,
Was away a bit longer than I expected - next time I will ask which Monday I can expect to be back :)
Will have a good look during the week and reply later in the week - thanks for everyones patience!

Posted 2 years ago
Top
 
Eligeol
Posts: 31

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.

Posted 1 year ago
Top
 
moreeg
Posts: 842

Hi Eligeol

Looking forward to part III. Wishing you and yours a happy and safe holiday season (even if you do have to spend some of it working).

Posted 1 year ago
Top
 
Eligeol
Posts: 31

Thanks moreeg - much appreciated as always! Keep well!

Posted 1 year ago
Top
 
Eligeol
Posts: 31

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 :)

http://i1340.photobucket.com/a.....70ec2f.jpg

Thanks again for all your help and patience!

Posted 1 year ago
Top
 



Topic Closed

This topic has been closed to new replies.