Last week, I was presented with a complex reporting challenge at work. We needed to take a list of grants and summarize them by organization (ok, that shouldn’t be too bad) and then include a list of the funding strategies for each organization (which took me an embarrassingly long time).
Hold the phone. Stop the presses! Embarrassingly?? Goodness gracious, self, that must have been my subconscious writing! I don’t think it’s embarrassing to get stuck. And I learned a heck of a lot by figuring out this problem. One of the reasons why I want to write about it is to zoom out and share that learning with my community. So, buckle up, friends! We’re going on a bumpy ride.
First, imagine you have a list of grants that looks like this:
| Organization | Program |
| Sam’s Pillow Palace | Direct Service |
| State Park Cabin Preservation Alliance | Recreation |
| Honeycrisp 4 All | Food and Agriculture |
| State Park Cabin Preservation Alliance | Environmental Justice |
But your end goal is a table that looks like THIS:
| Organization | Programs |
| Sam’s Pillow Palace | Direct Service |
| State Park Cabin Preservation Alliance | Recreation; Environmental Justice |
| Honeycrisp 4 All | Food and Agriculture |
There’s probably a way to do it with fancy computer coding, but that’s not a skill that I have and frankly, it’s not a skill that very many social movement orgs or nonprofits have access to either. It was me versus Excel, and turned into quite an epic battle.
The first step I took (after exporting my data) was making a Pivot Table. Why was a pivot table a good solution for this problem? Well, I knew I wanted to summarize my list of grants in two particular ways. First, I wanted to summarize by Organization – those would be the rows. Then, I wanted to summarize by Grant Area – those would be the columns. For each cell, I would know if an organization had been funded in that area, or not. The cells would count the number of grants in each category, so in my example if someone had been funded twice in Direct Service, the table would show “2” instead of one.
The end result looked like this:
| Direct Service | Recreation | Environmental Justice | Food and Agriculture | |
| Sam’s Pillow Palace | 1 | |||
| State Park Cabin Preservation Alliance | 1 | 1 | ||
| Honeycrisp 4 All | 1 |
Now all I had to do was turn those numbers into words. Should be a piece of cake, but it wasn’t! I got stuck in 2 rabbit holes. The first one was using the “IsBlank” function and not getting the results I expected. Somehow, Excel thought that my cell was NOT blank when to me, it looked as blank as my confused blank stare! I finally circumvented that rabbit hole by using a “length” calculation to count how many characters are in the cell. Somehow, testing for “number of characters = 0” worked better than “is blank = true.” I still don’t quite no why. But I moved on. Right? Right.
There were two Excel formulas that I had to employ to get this whole thing moving and grooving. The first one is known as “Concatenate” which is a fancy word for smooshing letters together. For example, I could concatenate “cat” and “dog” and get “catdog.” Or I could concatenate “Cat” + “space” + “dog” and get “cat dog.” The Excel formulation for that would be =CONCATENATE(“cat”, ” “, “dog”). (The middle quotes are around the space).
I also needed to use the “IF” formula. So, IF an organization had a grant under “Recreation” then we needed to concatenate “recreation.” If not, skip! So here’s how I did that, first in words, then in formula:
With words
IF Sam’s Pillow Palace has a grant in Direct Service, concatenate “Direct Service; ” otherwise, skip! IF Sam’s Pillow Palace has a grant in Recreation, add “Recreation; ” to the concatenate results otherwise, skip! and go on like that.
With words and formulas, kinda
IF the value in B2 (aka Sam’s Pillow Palace x Direct Service) is greater than 0, concatenate “Direct Service; ” [notice the extra space!], otherwise skip! IF the value in B3 is greater than 0, add “Recreation; ” to the concatenate results, otherwise skip! Etc.
How concatenate works
=Concatenate(thing 1, thing 2, thing 3) (don’t forget punctuation in each “thing”)
Pure formula
=CONCATENATE(IF(B2>0, “Direct Service; “, “”), IF(B3>0, “Recreation; “, “”), … and so on)
When I finally got it, I was like …
So what?
In a #RelationalDatabase #jargon (oopsie), this kind of task is always going to be hard to do (Unless you know a secret that I don’t know! And if you do, please tell me!) I think it might be easier in databases that are more into “tagging” (which can get scarily loosy-goosy, but at least flexible!). But that’s just a guess.
I can imagine that many folks reading along are probably thinking, “this sounds complicated and I don’t even track grants.” Fair enough. I do have a counter argument for how this technique can be useful for changemakers who are managing different types of data. Here are a few examples:
- You have a list of separate registrations for a set of political fundraisers. You want a list of donors and which fundraisers each person has attended.
- You have a list of households and separate membership records for each year. You want a list of households and which years they were a member.
- You have a list of actions and volunteers, each with roles and tactics. You want a list of volunteers and which roles/tactics they have participated in.
- You have a list of voting history over multiple elections. You want a list of people and which candidates they voted for.
The basic formula is you have a list of THINGS and you want to group by that THING and combine some wordy data into a list at the same time. I could imagine this skill being useful in all sorts of scenarios!
The task is mighty… but so are we
Right now, I’m heavily involved in supporting 4 wildly different organizations to make sense of their database systems. Each organization faces acute challenges and lately some of those challenges have been feeling especially prickly. There are two songs that have been circling through my head and inspiring me to stay the course – and I want to share them with all of you!
One, I learned through my organizing with EQAT. The words go like this:
The task is mighty, but so are we
https://www.riseupandsing.org/songs/tide-rising
This is what we were called to be
The storm is raging, but so are we
This is where we are called to be
The second, I learned from Rabbi Mónica Gomery
Healing is possible, let it be so
Healing is happening, may it be so
And we believe
that what we need most
is the hearts of each other
right here