Copy and Pasting Nightmare - Help with Formatting

Hi all. As part of a senior engineering design class I am taking, I need to gather data for 228 oil and gas wells and paste the data into Excel. The data is publicly available at http://cogcc.state.co.us/ (hope I'm allowed to post links?). My problem is that the data is formatted in a ridiculous way on the website, and there is no way to export it to excel or even as a csv. Attached below is a screenshot of the table the way it is formatted on the website.

Capture.PNG

The main problem I'm having is the way they overlay Oil data in black on top of Gas data in blue. They place two values in the same cell (I.E. Oil BOM and Gas Produced). When I attempt to copy and paste this data into Excel, I get all sorts of formatting problems. For example, under "Oil Produced", there is typically no value for Gas Flared, so the cell has the oil value on top and a blank value underneath. Therefore, when I copy and paste this column, the oil values have a blank row between each cell. At first I was just using the Go To function and selecting blanks and deleting them, but for some wells there is no oil production. Rather than inputting a zero, the website simply leaves the cell blank. Therefore, if I select all blanks and delete them it causes some actual oil production values to be deleted by accident and causes the data to become misaligned with the dates.

That is only the beginning of the problem. I am also having problems pasting water production due to the water disposal code being in the same cell, in additional to the blank cells. I feel like there may be a way to simplify this process using the filter button, but I can't even get that to work because all it lets me filter is the literal label "oil" or "gas", not any of the actual data. If there was a way to move the oil data so that it was separate from the gas data (in a different column to the right) that would probably help a lot, but I haven't found a way to do that yet.


I have attached a workbook that I've started. The first sheet is the format I need the data in. The second sheet is the format given if I copy and paste from the website. The third sheet is the format given if I copy and paste from the website using match destination format. Don't worry about latitude/longitude/well number stuff. All I need help with is oil produced, gas produced, and water produced.

Can anyone help me? The way I've been doing it is a combination of IF statements and manually typing in by hand, and it's taking me over an hour to do a single well. I don't have 228 hours, so there has to be a better way to do this. Thanks so much.

Hi and welcome to the forum

Please do not upload a picture of your file...rather, upload a sample of your workbook, showing what data you are working with, a few samples of your expected outcome is (manually entered is ok) and how you arrived at that. (exclude sensitive info). Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you Also, not all members can upload picture files (Company firewalls and stuff)

Thanks for the file, no problem

Im trying to match the data in MPD sheet with sheet6, and things dont seem match :*(
I assume that sheet6 data starts in row 90 in MPD (5/1/2006). If so, where do you get 47 and 3450 from? (F90:G90) Or am I not understaning you properly?