Replacing Workarounds - a Tale of Spreadsheet Evolution

A really satisfying feeling in tech life is when you can replace your workaround with something more stable. Something more common. Something more “out of the box.”

Spreadsheets are such a ubiquitous and stable part of life that you might not think that your workarounds had any hope of being replaced with something more straightforward. But incredibly, more amazing options seem to be delivered to us all the time.

There was the evolution from vlookup to xlookup.

There was the evolution from the arrayformula() to many more actual array-based formulas like ByRow(), which is my super-favorite formula of the moment.

There is the evolution that led to rows.com, an entirely new spreadsheet tool! (the promise of this one for me is the ability to write formulas that are actually API calls).

Resident Google Sheets expert and authority Ben Collins alerted me via his newsletter to a new formula evolution in GSheets that is going to replace oh so many different workarounds I have tried in the past for trying to combine two or more columns of data together.

Welcome Vstack() to the spreadsheet world!

Here is a real-life example of how this is going to replace a workaround in my work (all data has been fake-ified):

There is a non-profit I work with that supports families going through fertility treatments with money grants to defray the costs of treatment. Side-note: my family has gone through fertility treatments, and I would have LOVED to have some help with those costs! Anyway, they track their granting information in a common system used my many non-profits called Bloomerang. Bloomerang could not provide the kind of reporting and dashboarding that the non-profit really wanted, so we partnered to build them a spreadsheet solution to help them analyze their data.

Data comes out of Bloomerang in a way that we call “long". That means that each record from Bloomerang that represents a person gets one row in a spreadsheet. But guess what - we give out more than one grant per person. So we have columns in our spreadsheet (which were fields in Bloomerang) like “Grant #1 Date” and “Grant #1 Status” followed by “Grant #2 Date” and “Grant #2 Status”:

For part of their reporting, the nonprofit folx want to be able to see all of their grants on individual rows, meaning they want one row per grant - so if a person has two grants, they would want to see two rows for that person. We could copy & paste, we could use some filter formulas, we could use some array formulas, but now we have a built-in option - the vertical stack or vstack().

I still need to do a bit of a workaround to make vstack work in this particular example. The number of columns you are stacking needs to be the same, so I’m going to add in another “Name” column, and make it equal the same values as the original name column:

Check out that use of arrayformula in the Name Copy column - fancy, right? That means you only write that formula one time, and it feels in the data all the way down the column (because I LOATHE copy & paste/drag-down formulas, ask me about it!).

Now I can use my vstack formula to bring together the first set of 4 columns and the second set of 4 columns:

Uh-oh. An error.

One of the technology mindsets that will set you up for success: I love error messages.

Why did this error message pop up? Because I’m not just stacking the data that I currently see. What if NEW DATA comes in, as it always does? So we are stacking everything from row 5 down - like down to row a million or whatever. Don’t worry! If we only stacked our columns with all those blank rows, we’d see the first set of grants, then we’d see a ton of blank rows, and then the second set of grants. We don’t want that anyway.

So we’ll solve this by doing something we wanted to do anyway - sort the data so the names are grouped together. A simple sort() formula helps us with that:

OK vstack, welcome to the hood!

Spreadsheets, like all technology, are evolving. We benefit from this evolution.