Many of us use spreadsheets as simple databases, using them to store all the information we have about a particular topic. From time to time, it can be useful to present parts of this information to others, rather than sharing the entire contents of the spreadsheet.
Take for example information about a football team. You might store the names of each of the players, the substitutes, the positions they play, their player numbers, their contact details, and whether they have paid their subs.
However for the weekly programme you only need to list the starting players and their numbers (highlighted in green below) along with the substitutes and their numbers (highlighted in yellow below).
The players who aren’t in this week’s match would not be included in the programme. Nor would information such as the players’ phone numbers, or whether their subs have been paid.
To run the team throughout the season the manager needs a spreadsheet containing information about the full squad, including players not involved that particular week. During the season players may leave or join the squad, so the list of players changes regularly.
With the contents of the main spreadsheet being constantly updated, keeping the weekly programme up-to-date can become frustrating.
Use Combo Gumbo and stay ahead of the game
Combo Gumbo is a neat little app that will help you grab just the information you need, then combine it into a single list ready to share it with others. It’s one of a series of Doofas: Excel plug-ins that do things for you.
So, with our aforementioned football team, Combo Gumbo can make producing the weekly programme quick and easy.
The players’ first and last names and their numbers are stored in separate columns in the full spreadsheet.
We can use Combo Gumbo to merge these details into a single, dedicated column containing exactly what is needed for the programme.
Whether the team-member is a Starter or a Substitute is marked in the corresponding column with a Y or an N. This means that we can easily sort the spreadsheet using the Starter or Substitute column to quickly identify the player’s to be included in the programme on any given week.
How it works in practice
Before using Combo Gumbo the team’s spreadsheet might look something like this:
The spreadsheet contains all of the details about all of the players in the squad.
To prepare the information for the programme:
First, add a new column and name this column Full Name.
Then use Combo Gumbo to combine the First Name and Last Name columns (as shown below)
Next add another column and name this column Programme.
Combo Gumbo can be used a second time to combine the contents of Full Name column with the Number column, and place them in the Programme column.
So now the Programme column shows each player’s number and full name – just the information needed for the programme.
Finally, by sorting by the Starter column, and then by the Substitute column, we can produce the list of Starters and Substitutes (see screenshot below). Sort Z to A, so that the Y comes before the N.
We can then easily copy and paste this information into our programme ready for match day.
Using Combo Gumbo in three easy steps
Select the first column you wish to combine
Select the second column you wish to combine
Show where you want the combined information to appear.
Step 1: Select the first column
To kick off, open up the Combo Gumbo tool. Highlight the first column you wish to combine and then click Select. In our football team example, this will be the column that shows each player’s First name.
Note that Combo Gumbo will ask whether or not the column you’ve selected contains headings in the first row. In this example you would need to select Yes.
Then click Next to move to the next stage.
Step 2: Select the second column
Select the second column that you’d like to combine with the first. In this example this will be the column showing the Last name.
Notice that here you’ll be asked to choose what punctuation you’d like to be used to separate the information you’ve combined. In the case of the football programme, we’ll need each First Name and Last name to be separated with a space. So select Space from the drop-down list.
Click the Next button to move to the final stage.
Step 3: Show where you want the combined information to appear
Combo Gumbo will paste into the spreadsheet the contents of the two columns you’ve selected. So you’ll need to find or create a blank column in which this information can be pasted.
So for this example, I’d suggest adding a column to contain the Full name of each of the football players, as shown in the screenshot below.
Then highlight the Full name column and click the Select button.
Finally, you’ll be asked whether you’d like Combo Gumbo to Cut and paste or Copy and paste the results into the spreadsheet.
It would be useful to have columns showing each player’s first name and last name individually, as well as a column showing everyone’s full name. So, in this case, it’s best to select Copy and paste. This will create a brand new Full name column while leaving the First name and Last name columns as they are.
Click Next and you’re nearly done…
The Summary page will allow you to quickly check what you have asked Combo Gumbo to do.
Click Go and the first and last names of all the football players will be combined and pasted into the Full name column.
Pulling together the football match programme
The simple steps explained above can be used to combine any pair of columns.
The match programme needs the players’ numbers as well as their full names.
So to pull together all the information for programme, you simply repeat the steps above, but this time combine the Number column with the Full name column.
Again, you’ll need to create a new empty column in which to place the contents of the combined columns. The Programme column in the screenshot below, shows the combined contents of the Number column and Full Name column.
Sort the data so that the starting 11 are listed first, followed by the substitutes. Select the details of the Starters and Substitutes which are now in the Programme Column; then copy and paste them into this week’s programme.
Each week you just repeat these steps to ensure the Programme listing column is up to date, and resort so that that week’s Starters and Substitutes are ready for export to the Programme file for printing.
So there you have it – a quick and simple football programme. Back of the net!