Mr. Excel got me on the lookout for ways I can leverage good 'ol fashion Excel formulas in my day to day work. Tonight I found my first scenario where Mr. Excel's advice came in handy. Here's the problem:
The Problem
My boss sent me a spreadsheet with a few columns of data. However, one column was missing. He asked me to fill in that column by using data from database I had access to. There didn't appear to be an easy way for me to query the database and drop the results into the column. It was looking like I was going to have to run a query and manually place each value in the correct row in the column. Yuck!
The Solution
Here's what the data looked like (scrubbed, of course) before I got started:
I started off by dropping the data from the database in another part of the spreadsheet. It's important that I kept that data as its own little table.
I did two things to the data. First, I lowercased all the names. I did this to make the data a little trickier to work with (you'll see how that works in a few moments). Second, I sorted the data in descending order. This step is required - things will break in strange ways if the data off to the side isn't sorted properly.
To actually fill in the missing column, I copied the following formula to each row in that column:
=VLOOKUP(LOWER(B5),
$G$6:$H$12,
2,
TRUE)
The key to this formula is vlookup, a function which will browse through a set of data and pull you back the appropriate value given a lookup key. In this case, the key to lookup is lower(B5) and the data set to check is $G$6:$H$12. The third argument 2 says which column to pull back when you find a match - the 2nd column in this case. The final argument, TRUE is there for reasons I can't explain. Without it, things don't work properly.
Here's the final result:
This spreadsheet is nice and maintainable. If the database values were ever to change, I could drop in a new lookup table off to the side and the sheet would automatically correct itself.
This was fun! I'm so keeping an eye out for more ways I can leverage Excel.
Ben,
ReplyDeleteRemember, "vlookup" is for use when searching for a corresponding entry in a vertically-oriented data set (hence the "v" before "lookup"). You would use "hlookup" in a horizontally-oriented data set.
Excel has a gargantuan number of really useful formulas and tools to make spreadsheets more user-friendly.
Thanks for the tip Gareth!
ReplyDeleteI know this is basic stuff, but seeing it actually work and having it actually save me time was such a thrill.
What other functions do you find yourself using a lot?
I used to have a spreadsheet inherited from a team member which contained a button at the top labeled "import."
ReplyDeleteOvernight a process would run dumping desired data from a database into a CSV file. Once "import" was clicked, this csv file was opened by an excel visual basic macro and read into a second worksheet behind the main one. The values were then filled in by copying from this raw data worksheet into the first worksheet.
Using a second worksheet in the same book kept the raw data away from the common (accounting) user and allowed for some undesired values from the database dump to be skipped.
Thanks a whole lot for going to England, by the way. Now my wife wants to go. Guess I'm still luck she has no desire to ever return to New York...that place is one huge wad of chewing gum.
Using "behind the scenes" worksheets to process raw data is a trick I use quite often, especially when other people are going to be using the sheet and don't need to know all the machinations that make it work. If-Then statements are very handy, subtotals and sumif statements can be handy too. I don't know how to program in Visual Basic, so I use the macro recorder quite a bit.
ReplyDeleteConditional formatting is a great weapon to have in your arsenal. Beyond changing cell or font colors, you can use it to "hide" information by making font the same color as the cell.
I have extremely limited knowledge of pivot tables (I don't work with databases very often), but whenever I see them in action, it reminds me how powerful a tool Excel really is.
Ben -
ReplyDeleteThat's the best description of NY I've ever heard! I'm with your wife on this one.
I appreciate the VB success story. I always forget that there's a full programming language that's relatively easily accessible in Excel.
Sorry about making England look fun. I'll try to make our next get away look terrible to make it up to you.
Gareth -
ReplyDeleteThose are fantastic tips! I'm so going to use them.
I never thought I'd hope that I'd get a request to analyze a spreadsheet of data. Thanks.
(And when I get stuck, I now know who to call ;-))