As much as I enjoy heavy lifting in real life, I don’t enjoy it one little bit on a computer. I’m…efficient (we’ll say efficient rather than lazy) that way. In a previous edition of Enspired, we explored some helpful tips to excel when using Excel 😉. Now, we are going to add even more to your bag of tricks, setting up your Excel game so that the computer is doing more of the heavy lifting than you are.
Sarah Compton
Editor, Enspired
Tip 1: Combining Lists
Studio Romantic/Shutterstock.com
There are a few ways to combine lists or pull specific data from one list to another, but I like XLOOKUP. Here’s how to use it:
In the cells/column where you need the data filled in, type “=XLOOKUP(”. You’ll likely get a prompt to help you out.
The first value you’ll fill out is the “lookup value.” This is the value you want to search for in the list you’ll be going through. In my workflows, it was often an API.
The next value is the “lookup array.” This is the column, or set of columns, that you’re searching through to find your “lookup value.” In my workflows this was also often a list of APIs.
The next value is the “return array.” This is the value you want to pull and fill in for your empty cell. It could be production numbers, completion data, etc.
These values are separated by commas in the formula, and once you’ve selected them, you can close the formula with the closing parenthesis and call it good!
Extra, extra: There are some other options you can use that are helpful, such as “value if not found,” which is fairly self-explanatory, “match mode,” and “search mode,” which let you set parameters on the match and search methods (i.e. exact matches, greater/less than, etc).
You can get help on the different formula aspects by hovering your mouse over the word when it’s time to fill in that part of the formula. Then, you can use a filler value to move forward in the formula.
Take a look: I’m including a screen capture from my Excel sheet of some values I made up. In this example, I want to pull values from column A to column F. I’m looking for values in E that match in column B to fill out my data.
Sponsored
Enhanced oil recovery offers decarbonization opportunities in CCUS
"Old reservoirs, new tricks": The future of CCUS utilizes depleted oil fields and EOR to create subsurface CO2 repositories.
That workflow might not function correctly if your formats are off. Recall the writeup I did about APIs? Not to keep knocking APIs, but they can cause issues when you’re combining data.
Here are some steps to get your APIs to work for you:
Match it up: It’s good to make sure the columns you’re matching are the same format, or the formulas won’t work.
To check the format, highlight a whole column, then right click any cell in the highlighted column.
Scroll down to “format cells” in the pop-up list. APIs can be either text or number for most matching uses. I usually select number and make sure it doesn’t go into scientific notation.
Zero in: Colorado APIs begin with a zero, which Excel tries to “helpfully” delete because in normal numbers, you don’t need a leading zero.
It’s purely a stylistic/type-A thing because rarely do you actually need, in the strictest sense, that leading zero, but I want it there because it should be there! You have a few options:
Just let the zero go. In my decade of oil and gas work, I have not been able to do this a single time, even though it’s hard to completely justify being that particular. Sometimes, you might refer to a well by its API in an analysis, and then you’ll be glad you kept the zero, but often, well names or pad names are used in visuals and analyses.
If you’re as neurotic as I am about this, you’ll want to keep your leading zero but still have APIs as numbers. To do this, you can select the “custom” format type and input 10, 12, or 14 number signs, depending on how long your API is.
Perhaps in another newsletter, I’ll cover how you can enter, or remove, the dashes in APIs, but for now, I hope you feel like your data management has leveled up!
And if you want to keep leveling up your tech and IT knowledge, there will be a ton of great energy tech and digitization networking opportunities at IMAGE '24 in Houston next week!
Apply for a free Exhibitor Guest Pass to access the various booths, poster sessions, and networking events available. Use priority code GP24IMG and select AAPG as your host organization during check out.
👍 If you enjoyed this edition of Enspired, consider supporting AAPG's brand of newsletters by forwarding to a friend or colleague and signing up for our other newsletters here.
➡️ Was this email forwarded to you? Sign up for Enspired here.
AAPG thanks our advertisers for their support. Sponsorship has no influence on editorial content. If you're interested in supporting AAPG digital products, reach out to Melissa Roberts.
.png?upscale=true&width=220&upscale=true&name=Sarah-Compton-Headshot-Signature%20(1).png)
