My friend and co-worker (who’s amazing art was featured in digital self care vol iii) came to me with a data conundrum last week. She had a list of 39 prospective grants, and she needed a list of every grant we’ve ever made to those organizations to assist in the grant review process.
This is a great example of a common data query – for example, you could have X volunteers and you want to know how many volunteer shifts they’ve completed. Or x cities and you want to know their population a different year intervals. Of course, your data need to be arranged properly to perform these queries, but if they are, you can extract the information you need and do a little “excel magic” to get results – and fast!
Essentially, the task came down to “I need to filter a list by another list.”
I think it’s helpful to review the solutions we DIDN’T pick (and why!). Don’t be discouraged by the ‘bad ideas’ cartoon – I only included it because I thought it was so cute!
My colleague could have done a search for each organization and manually tabulated (or copy and pasted) their grant history. That would have been incredibly onerous. Probably several days of tedium! That’s what she thought she might have had to do before we put our heads together.
I tried to brainstorm ways to do the task in Salesforce. The only solution I could come up with was to run a report of Grants and add a filter for “Name = Organization” 38 times, one criterion for each organization on the list. But that seemed silly, time consuming, and prone to mistakes. Better than the manual version, that’s for sure, but not the quick and smooth results as I was hoping for.
There was a more complex way that I could do it, which would have taken some configuration but would have made the task easy to replicate if this was a query we had to run often. I could have made a checkbox field, written a formula or a process to “check it” if there was an active grant under review, and then easily filtered any report based on that field. The problem is, that would have added more customization to our Salesforce, which seems excessive for a one-time request. Plus, it’s more work than the reporting method that I already ruled out for complexity’s sake.
Things are looking up!
Ok, so we explored three not-so-great options to solve a problem. Now, let’s look at the solution I *actually did!* It’s called a VLOOKUP. They may seem intimidating at first (and this post isn’t going to take the form of a tutorial – although, here’s a good one) but I promise, you can master VLOOKUPS with a little practice and determination, and they may become your best data friend. Just ask my former colleagues and VLOOKUP all-stars Sarah and Caitlin!
A VLOOKUP is a very powerful function in Excel and Google Sheets that lets you create a “Lookup Table” based on a common piece of information, and then scan your data for a match. For example, if I had a list of People and Congressional Districts, and a separate list of Congressional Districts and Representatives, then I could scan the data for the common information (Congressional District) and paste in the name of the Representative. VLOOKUPs are a tried and true way to make two spreadsheets talk to each other, as long as they share a column in common.
So, first I created a list of my target organizations. This was easy, because they had a grant in progress within a certain program area, and we have pre-built reports for that. Voila! 39 Organizations, spelled the exact way they exist inside of the database (this is important!). Export to Excel. Done.
Then, I exported a list of all grants in the system. Boom. Thousands of results. Good thing I can use my VLOOKUP super-power to sort them for me!
Then, I used a VLOOKUP to scan all of the grants, and identify the ones that had an Organization in common with the target Organizations list. Easy!
The, I used a filter to filter out all of the grants that were NOT to a target organization. And I copied and pasted my new, filtered down list into a new spreadsheet – exactly what my colleague was looking for.
Voila! 5 days of work in 5 minutes!
Some closing thoughts / caveats…
- Believe me, I learned this the HARD WAY (lots of CTRL + Find and Copy + Paste) data between locations before I realized that I could automate it. It took me almost a year to get comfortable with VLOOKUPs. Now, I can whip them out in no time flat! If I can learn it, YOU can totally learn it!
- Automating isn’t ALWAYS the answer. Sometimes, there’s real value in sorting through data because you’ll learn more about what’s in your system and sometimes you can identify discrepancies. I knew this was the right solution for us because we were preparing the data for other reviewers, not for our own eyes.
- Sometimes we want time to speed up and sometimes we want time to slow down. “5 Days in 5 Minutes” is only a good thing if it allows you to more of what you love with the time you have left over. I am suspicious of 21st century productivity standards and a drive to “efficiency” if it’s not tied to digital self care.