Nerdery,
I do not post here often but lurk quite a bit and know there are some excel users much better than myself. So I am working with a Lease Operating Statement (If not familiar with O&G, kind of like a Profit and Loss). The way the software outputs the data is a spreadsheet, about 60,000 rows. It is the same "table" recurring every 148 rows. To throw a curve ball, rows are not full to just pivot, and there are row "breaks" with headers, etc, since it is exporting 3 "pages" per "table".

To explain this better:
"Table" 1 is B13: O145
"Table" 2 is B161: O293
... to row 57688

There are breaks/empty rows within these tables, all recurring at 148 rows.

What is the best/easiest way to get this this condensed to be able to pivot, or any other way to where it is workable? This will be something I battle quarterly, so having a better solution than deleting stuff out to be continuous would be much appreciated. If yall need visual aid, let me know, I would have to scrub some stuff.

Thanks in advance

as I understand...

1) each 148 rows is its own data set
2) there are empty rows between data sets which help visually separate each "table" as you say...?

If 1 and 2 are correct, then I have some clarifying questions

a) each quarter/month or whatever frequency the report is run, does it add another 148 rows to this so it contantly grows, or you get a whole replace of all 60K+ rows each time?

b) do you need to query within each set of 148 or do you want to work with the data in all 60K rows?

c) what do you need to do with this data when you say "workable"

CapCity12thMan said:

---

as I understand...

1) each 148 rows is its own data set
2) there are empty rows between data sets which help visually separate each "table" as you say...?

If 1 and 2 are correct, then I have some clarifying questions

a) each quarter/month or whatever frequency the report is run, does it add another 148 rows to this so it contantly grows, or you get a whole replace of all 60K+ rows each time?

b) do you need to query within each set of 148 or do you want to work with the data in all 60K rows?

c) what do you need to do with this data when you say "workable"

---

CapCity,

1) yes, every 148 rows starts a new set (gas well) costs
2) yes, more or less

a) good question; the months are the columns, with a trailing last 12 months, Year To Date, and Cumulative. The format will mostly stay the same; the column width will stay the same as old months drop and new months are added. The height/length will change when new wells are added. I figure whatever is done can just be carried down to include the new "tables" (wells) as they come online

b) Preferably all 60K rows, because I will be comparing similar rows across the "tables" and then all rows within a "table". Meaning looking at Chemical costs for all wells (row 110 and then every 148 rows) by months (columns), and then Chemical costs vs all costs within this "table".

c) by workable, I am thinking Pivot table, as that is what I know and will spit out what I need with its filtering; however, if there is another route that is better than I am not aware of, please have at it.

I use Google Sheets, but most can be used in Excel with some minor differences, but my first inclination would be to use the QUERY() function where you can write some SQL as you need to. Now, if you dont know SQL that might be a small challenge, but I would write it to output the data you want to then pivot table from.

Still not entirely sure how this data looks and what you want to do, but here are some ideas:

https://docs.google.com/spreadsheets/d/1SWRmuPfYx4Ul4BAGoVnHSKStmxmmf4LFhsewKhwxumM/edit?usp=sharing

raw data on tab 1, stuff on tab 2

Nm

Have a programmer write you a script that converts the output file into a file that you could easily manipulate in Excel

It looks something similar to this, except filling out the range(s) specified. There are significantly more sections, and they are not all the same amount of rows (3) as shown; however, each segment is the same size for every well (ie the Expense, Revenue and Total segments are the same size for every well). This would be for the first well, then repeat for Well B, Well C...

A VB or python script should take someone a few hours to crank out and get the data how you want it.

dam

It seems that outside learning some coding (I do want to learn) or getting someone to code it, there are no formulas or anything I can currently do? Just manual formatting?

It would have to be a combination of formulas and VB more than likely. If you send me copy and explicit information i can take a look at it and whip up a python script that you can run as an executable. Free of charge if it's going to take 1-2 hours.

dam

Look into Power Query/Get and Transform (can't remember what it's called, they like changing the name) that's built into excel. Can set up things like filling null rows/columns, setting up fields you'd need for pivots, etc. And then it remembers the steps so you can run it each month/quarter without setting it up again.

And power query is graphical, so you don't need to learn to program. If you're good with pivot tables you can probably figure it out.

dam, I will make a redacted sample tomorrow and share it.

aggietony, thanks I will look into it.

aggietony2010 said:

---

Look into Power Query/Get and Transform (can't remember what it's called, they like changing the name) that's built into excel. Can set up things like filling null rows/columns, setting up fields you'd need for pivots, etc. And then it remembers the steps so you can run it each month/quarter without setting it up again.

And power query is graphical, so you don't need to learn to program. If you're good with pivot tables you can probably figure it out.

---

I've been using Power Query a bunch lately. It is a sort of SQLish query language that I've found to be really useful. It would be much better for reporting than pivot tables.

I think you're going to have to use VB or something like that to get it formatted in a useful way for Power Query (or whatever they're calling it).

Then you can report on it to your hearts content. I would dump all the total/summary rows, obviously get rid of all the formatting rows and the YTD and cumulative totals (they're just going to complicate your calculations. If "net income" and "operating income" are summaries of the other columns, I would get rid of those too. It'll be more useful to recalculate those later.

I found some VB code snippets that would help in converting the data. I'll check back when you post a redacted example. The good news is that its imminently doable.

I was thinking PowerQuery might help you also.

It has some great tools for data cleaning and might be able to help you out here.

Good intro video here - check the comments for link to DL the data file so you can work along:

That definitely took longer than I anticipated. A small(er) sample of 21 wells/assets/areas is here: https://docs.google.com/spreadsheets/d/10NW4ctnvoCQz5LVZwV70lhsy_rY20ntr/edit?usp=sharing&ouid=117035671587575926730&rtpof=true&sd=true

Each well/asset takes 3 pages and this recurs every 148 cells. However, for area assets, they have the same amounts of rows but there are different breaks between pages.

Any insight on making this workable/navigable outside of just formatting it down, would be thoroughly appreciated. The easiest way to explain that, is have a pivot table where various filters can be used (Line Item, Operator, etc).

dude, its friday afternoon...yikes.

I know it was Friday afternoon, just first chance I had to scrub it to post an example set

Bump for the weekday crowd

Just an FYI... your sample dataset doesn't repeat every 148 rows. There are a few sets that are missing some 2 rows of property ID info per page, making them only 142 rows. EX. Property ID 176440.

Ultimately it isn't a huge deal as long as you know that going in as all the other fields look consistent on a first pass but it doesn't look like you would want to use anything that is just a factor like that.

Correct, some of the assets are off, but if you get back into the wells specific, it is. So that throws off just running with one thing.

OK, I haven't fully done it, but I don't think this requires anything very fancy. What you want to do are a few tasks:

1. Remove rows with unnecessary labels or blanks
2. Add enough tags so that when you pivot you get useful information (e.g. which well...)
3. Pivot so you can easily summarize

This isn't that bad. Here's how I would approach:

1. Add tags - this is actually the hardest part. You need to figure out how much of the stuff in rows 8-10 you need. For each point of data you need, add a column at right. Then, you'll need to write an if statement. Reference the first cell that has data you need, e.g. B8 with 'Property: #####'. You need an "If" statement using the text. It'll be something like if(left(8,B8) = "PROPERTY", mid(B8, 10, 8), [reference cell above it]). Warning, this formula is totally untested and done from memory. You'll need to tweak it I'm sure. See description in next paragraph.

What this does is parse the cell, if it's a new "Property" tag it takes the property ID number, and if it's not it takes the value above it. So, when you drag this formula down, it's going to repeat the Property ID number until it gets to a new one, then it will replace that number with the next one.

Repeat this process for each data tag you're going to need.

2. Paste values - so all those great formulas you wrote? We're about to break them, so paste values for all of those columns. But make sure to keep a version somewhere that will let you just copy/paste next month.

3. Delete extraneous rows - there are a few ways to do this. First, find a column that has blanks where you need them. Based on the example, it likely has to be A. Go to Special, Blanks, then Delete Rows. OK, that was easy, but what about the totals? From here, I'd suggest filtering the entire sheet. Sort by Column A, which will put all of the totals and labels together as they start with *. Delete those rows as well.

4. Pivot what's left and do some checks.

It looks hairy, but after you set up those formulas it should only take about 5 minutes each month/quarter. Let me know if any of this is unclear - I have to do stuff like this all the time and it's how I'd approach it.

Edit to add - one of the nice things about this approach is that it doesn't care what the repeat is. It just looks at every cell in the row with descriptive data and changes when there's a new one. So the 148 vs. other row count thing becomes irrelevant.

Thanks so much. This is effectively the approach I had taken with the exception of the provided formula, which looks quite handy. I will give it a go and see how it goes. I do appreciate your time and input.

One comment/question on item 1, is your property ID always 8 characters?

If you aren't sure I would amend to if(left(8,B8) = "PROPERTY", right(B8,len(b8)-10), [reference cell above it])
That will grab everything to the right of the 10 characters of "property: "

Property is not always 8, but a large majority. There are some 2, 3, 4, 5, 6, 7, 9+

This in VBA will probably did what you need.

ETA Saw the bit about the tags. That would be easy as well. You could easily just add the columns you need, then iterate over the rows adding that tags based on row values or variables you set for information you pick up as you iterate (well #, property ID, etc). When done, run the row deletion for cleanup. I can't get to Google sheets on my work network, otherwise I'd just write that too. Honestly I could get this down to a couple of clicks in under an hour.

Go to Options -> Customize Ribbon and check Developer under Main Tabs on the right

In the Developer tab added to your ribbon, click Visual Basic on the far left.

In the Project page on the left, right click on your workbook name, go to Insert, and click Module.

Copy and paste the code below into the module.


In the function, you just need to specify a column to know will have something in it on the last row. If you use a letter, put it in quotes ("B"), but you can also use a number.

In the sub, you just need to specify the column you want to check and what you're checking for. If you need to check multiple columns, set them up the same with the ActiveSheet.Cells... = Condition format. Separate the conditions with "And". If you're looking for specific text, put it in quotes ("The text you're looking for"). If you need wildcards, that's a little different, but I can help there as well if this isn't enough


Function LastRow()

LastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, 'Column you know will be populated on last row') .End(xlUp).Row

End Function


Sub RemoveRows

Application.ScreenUpdating= False

Dim LastRow As Long
LastRow=LastRow()
Dim r As Long

For r=LastRow To 1 Step -1

If ActiveSheet.Cells(r, 'Column you want to check').Value='Value you want to check for' And 'Any other conditions here' Then

ActiveSheet.Rows(r).Delete

End If

Next

Application.ScreenUpdating=True

End Sub

Thanks so much. I will give it a go.

No problem. Here's a little more for tags, though it isn't as cut and paste. When sitting with the columns, remember that it will add columns and destinations or columns you want to check will have moved from when you first start.

Application.ScreenUpdating = False

Dim i as long

For i =1 to however many columns you need

Activesheet.Columns("column you want to insert before").Insert

Next

Dim r As Long
Dim SrchRng as Range
Dim var As String
Dim var2 As String

For r = 1 to LastRow() 'This requires the LastRow() function posted earlier

'This will search for text that signals you have a property or tag that is important
(ie "PROPERTY: " in "PROPERTY: 12345678")

Set SrchRng = ActiveSheet.Rows(r).Find("Label you're parsing for", ,Excel.XLFindLookIn.xlValues, Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByColumns,xlNext)

'If the Find above found the text, this will parse for the tag you want, assuming it is to the right if the label

'The label you're parsing for should include everything to the left of the tag value including spaces (ie "PROPERTY: " includes the space at the end)

If Not SrchRng Is Nothing Then

var=right(SrchRng.Value, len(SrchRng.Value)-len("Label you're parsing for")-1)

Else

'This assumes you're on a data row and want to add a tag that was picked up earlier. This may not be true, but you can as conditional logic by changing the Else above to an ElseIf and adding a conditional test and "Then" similar to above where the If/Then starts

ActiveSheet.Cells(r,'Column to place var tag').Value = var

End If



'This will check a cell in the current row for a value (ie "Taxes" in the Description column") and set a tag accordingly

'You can add an ElseIf with more logical tests to check for different values and add tags accordingly (ie "Tax" if "Taxes" is in the cell, "Operator Expense" if LOE is in the cell, etc)

If instr(ActiveSheet.Cells(r,"Column you want to test").value, "Value you're looking for")>0 Then

ActiveSheet.Cells(r, "tag column").Value="Tag"

End If

Next

Application.ScreenUpdating = True

ABATT, thanks for all that. I am working through it. I do not have much experience with/in this, so it is slow going.