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:

OrganizationProgram
Sam’s Pillow PalaceDirect Service
State Park Cabin Preservation AllianceRecreation
Honeycrisp 4 AllFood and Agriculture
State Park Cabin Preservation AllianceEnvironmental Justice

But your end goal is a table that looks like THIS:

OrganizationPrograms
Sam’s Pillow PalaceDirect Service
State Park Cabin Preservation AllianceRecreation; Environmental Justice
Honeycrisp 4 AllFood and Agriculture
See how I combined the two State Park Cabin Preservation Alliance into one row, and showed both Grant Areas separated by a semicolon?

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 ServiceRecreationEnvironmental JusticeFood and Agriculture
Sam’s Pillow Palace1
State Park Cabin Preservation Alliance11
Honeycrisp 4 All1

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
This is what we were called to be

The storm is raging, but so are we
This is where we are called to be

https://www.riseupandsing.org/songs/tide-rising

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

6 thoughts on “summary offense

  1. My partner recommended your blog and I absolutely LOVE it!! I’m on the data and tech team for a small nonprofit in Baltimore and your posts are constantly making me laugh and shake my head with mutual understanding.

    This post provides such a great description of how to systematically solve a technical problem that is new or challenging using the tools you are familiar with. It also provides a great framework for folks who are new to data manipulation. Definitely sharing this with the rest of my team!

    I know it wasn’t the main point of the post, but in case it would be helpful to have another solution that allows you to skip the pivot table step altogether, I put together this google spreadsheet with an alternative approach: https://docs.google.com/spreadsheets/d/1wuBzh_tmhXZ6BlcPm02zQy603Ne8-O4KMf66_om4dsc/edit?usp=sharing

    This one uses =UNIQUE() which will return a unique list of the organizations, then it concatenates the programs from the first table using a combination of =FILTER(), which filters the list of all programs based on the organization in the first column, and =JOIN(), which allows you to concatenate a list of values with a predefined delimiter–in this case “;”. The filter function is super powerful in general and can be combined with any other function that accepts a range of cells!

    Like

    1. Billy! Thank you so much for engaging so thoughtfully and for sharing this brilliant solution. Would you be up for collaborating with me and expanding this comment into a follow up post? I’m grinning from ear to year 🙂

      Like

      1. Love the post, as always! Also, you can do unique lists in Excel like Billy suggested with the Google Sheets Unique formula, by hacking the Index formula. One of mine looks like this:
        =IFERROR(INDEX($F$3:$F$6000, MATCH(0,COUNTIF($J$2:J2, $F$3:$F$6000), 0)),””)
        And here is a screenshot:
        2020-02-24_1132

        Like

  2. Oh I love Billy’s point about JOIN() – I wouldn’t have thought of that! I came here to say I’m a big fan of & instead of the lengthy/jargony CONCATENATE() — I use this a TON and find it so much easier to just type =A1&”, “&B1. Maybe better for different use cases, but in case it helps!

    Like

  3. Hi Samantha!! Sorry I’m just seeing your reply now, I would be honored to contribute to a future post! I think it would be cool to do a three-way collab with Emily as well. I’m so used to using Google Sheets that it would be helpful to learn how to hack Google Sheet only functions in Excel.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s