Buckle your seatbelts friends, because today we’re going for a nerd-tastic spreadsheet adventure. Pivot tables? We got ’em. Vlookups? Needed those, too. “If” statements? Yep. Concatenate function? You bet. It’s going down! And then it’s going back up… when I upload my results back into my database. If your impostor syndrome is kicking in, tell her to simmer down. You’re in good hands, and you can totally do this!
Earlier this year, my job started a process to divide our contacts into categories so that we could make different communications channels based on different themes or interests. So, we could say, these people like cookies, these people like ice cream, and these people like BOTH cookies AND ice cream. (Meeeeeeee!) (Remember: we’re a foundation that gives out grants, so I’m going to use grants as an example in this post quite a bit!)
The problem is that inputting all of that data is a huge project, and not very fun. And our data were organized in such a way that it was tricky to manipulate. The only way to figure out what category the Organization cared about was to look at their Grant History. There could be dozens! Good thing I had my secret weapon (excel!). It only took me a few minutes to manipulate the data I needed, and zip it back into the system.
Pre-req: What the heck are Multi-Select Pick Lists??
Multi-Select Pick Lists are like the “comic sans” of Salesforce data. They are considered not elegant or sophisticated, but sometimes they are helpful for people just starting out! As you can see in the image, MSPLs are good for being about to “select all of the above.” We are using them for our contact organizing because people tend to be interested in more than one topic. If you want to learn more about segmenting contacts in Salesforce, check out this resource. Or… this article on why many people think MSPLs are baaaad newssss. One thing you’ll see is that if you export the data from a MSPL, each category will end up separated by a semicolon. Which can also cause a headache when you are importing or updating info! More on that soon….
Let’s pull the data!
The data we needed about how to categorize an organization or a person is actually stored in our grant history. So if we know a person is connected to an “ice cream grant” in our system, then we know that person is interested in ice cream so they should hear about stories that have to do with ice cream (yummmm!). So, step one: Export all of the grants and their categories. We’ll need those later!
In our data pull, we can see all of the organizations and their program areas, but we can’t easily summarize how many grants went to each program, or how many programs an organization may be interested in. We will need another tool for this type of summarizing, called a Pivot Table!
If Pivot Tables are new to you, don’t worry! I’m breezing by some of the technical parts in order to tell this story, but I’ll do a deeper dive another time.
A Pivot Table is a way to summarize information into categories and create subtotals. Here’s a photo of what I ended up with. Looking more closely, we can see that Org 1 is interested in Ice Cream and Org 7 has had grants for both Cookies and Pie! For those of you who want to learn more about Pivot Tables, here’s a great resource. (Ooops – these numbers don’t match the screenshot above, but you can still get the general idea of how pivot tables work!)
This pivot table is a good start, but it still doesn’t enable us to track interest areas in a list, separated by semicolons, which is what we need!
IF… you’ve got it, flaunt it!
That’s where my “If” statements come in – another intermediate/advanced Excel feature. What I need is “IF” ice cream is greater than 0 (meaning there’s at least one Ice Cream grant in the system) then include “Ice Cream” in the interest list, followed by a semicolon. If not, just put nothing. So I used a formula that looks like this:
=IF(C2>0, “Ice Cream;”, “”)
Here’s what my results looked like…
Lastly, I had to combine the data so that if “Cookies;” AND “Pie;” were both included, then they would be next to each other in one cell instead of in two different columns. So I used the “Concatenate” function to join my data. I’ll spare you the gory details, but last (but not least!) I used a VLOOKUP to connect the organization name back to the unique ID stored in Salesforce, so that I could upload my changes back into the system, now that I knew what categories to include. This saved me weeks worth of data entry!!
I gotta keep this quick, because I’m rapidly approaching being late for my physical therapy appointment. But what I want to show you here is how you can use different types of Excel features to help you get to an answer that would otherwise take you TONS of time.
For social movement leaders, we NEED time. Time for agitation. Time for organizing. Time for our families. The last thing I would want is for you to waste such a precious resource re-typing data when Excel can take care of that for you.
That’s why I do what I do!
Do you want to learn more about these Excel features? Leave me a comment so that I know where to dig in next time. And no matter what happens, I’m here for you in your time of spreadsheet need. Just write me a note through my Dear Spreadsheet Whisperer advice column so that we can get you squared away, and hopefully have some nerdy fun in the process.