48
In The Club
Run a club or society? Here’s how your tech can make it easier
11 – 24 May 2022 • Issue 631 Next issue: Transfer email addresses of membersUntick ‘My list has headers’ and Excel will remove all duplicate items, leaving you with a single
master list containing all your members’ detailsMerge two membership spreadsheets
R
unning a small club is an admin
challenge in itself, but what happens
when two clubs merge and you need to
create a single master Excel sheet for
members? Thankfully, Excel has tools
to help you manage this process,
although you’ll need to do some work
beforehand. In our example, we’ve got
two membership spreadsheets: the
original and the one from the second club
that we need to merge into the first.Reformat the data
In our original membership list, we
recorded information in columns in
this order: name, email, phone. However,
the other club recorded information in
this way: first name, surname, phone,
email. Our first task is to reformat this
data so it’s in the same form as our
original. It’s important that both
spreadsheets have the same header
names. If they’re inconsistent (‘email’
and ‘email address’, for example), Excel
will have trouble working out how to
merge items.
We’ll start with the second club’s
spreadsheet. We’ve created a new column
called name in column E. We can then
use the concatenate formula (seescreenshot above right), which joins
multiple text items together. Into cell
E2, we entered the formula
=CONCATENATE(A2, " ", B2).
This tells Excel to take the first name
from column A, add a space, then take
the surname from column B to create a
single field with both names in it. We
then copied and pasted cell E2 all the way
down column E to get the full name of
each member.
If we wanted to do the reverse and split
a single field containing two names into
first name and surname, we’d use a
different formula. Assuming the name
was in cell A2, we’d create a formula
for a blank cell that looks like this:
=LEFT(A2, FIND(" ", A2)-1). This tells
Excel to take all the characters to the left
of the space in the name (the ‘-1’ tells it to
ignore the space itself).To get the surname, we use the formula
=RIGHT(A2, LEN(A2)-FIND(" ", A2).
This does the same thing but takes all the
characters from the right of the space.
Next we’ll create a new sheet with the
same headers as our original (name,
email, phone). We can then copy and
paste the relevant data from the sheet
we’ve just modified, so everything is
in the appropriate column. When
pasting data that’s derived from a
formula, click the clipboard icon after
pasting and select ‘Values only’. This
copies the data as it appears in your
sheet, not the formula. We saved this
sheet as formatted new data.
It’s worth checking the list you’ve
created for any obvious errors. If you spot
something, this is the time to correct it.
Then we can merge the data.Back up and remove duplicates
Make a backup of your existing member
list, just in case anything goes wrong.
Then open it in Excel, and copy and paste
all the newly formatted data from the
other sheet into the bottom of your main
membership list - don’t worry about the
header information.
Next, click the Data menu (^1 in our
screenshot left) and select ‘Table tools’,
followed by Remove Duplicates. Untick
the box ‘My list has headers’^2 , then click
OK. Excel will remove all duplicate
entries, leaving a single master list that
contains all the data.
Once you’ve done this, it’s a good
idea to sort the data by the email header,
and scan through it to make sure Excel
has removed all the duplicates. If you see
any, remove them manually. You now
have a single spreadsheet containing
all your data.This formula
takes the first
name, adds a
space, then adds
the surname to
create one field
with all the data1
2