Three Excel files for the supporter list: The nature of that awkwardness and how to clean it up
When you open the "Supporter Association" folder on your computer, you see a list of files with very similar names: "SupporterList_Latest.xlsx," "List(Election)_Final.xlsx," and "NewYearsCards2024.xlsx." For a moment, you hesitate, wondering which one to open. You hesitate, and in the end, you always open the same file you usually do.
How many times have you repeated this small moment of hesitation?
◆ The name didn't come to mind
At the reception for a supporter association gathering, someone who hasn't shown their face in a while greets you, saying, "Oh, it's been a long time." You recognize their face. You know them, but their name just won't come to you. You instinctively try to cover it up by saying, "It's been a while," while your mind is frantically flipping through the list in your hand. They aren't on the attendance list. Their name was on the membership fee list, with their address from last year.
I don't think many people can put that fleeting moment of awkwardness into words. The other person remembers you. And they remember you with a sense of support. Yet, you can't even recall their name. The feeling of regret lingers for the rest of the day.
For a politician, remembering people's faces and names is synonymous with trust. That is precisely why the moments when that becomes hazy are more than just simple forgetfulness.
◆ The invitation arrived in front of a memorial portrait
What hurts even more is sending mail to the wrong address.
You sent out the general meeting invitation as usual. A few days later, you get a call from the family: "My husband passed away last year..." Holding the receiver, your mind goes blank. Out of the three lists, only one reflected the mourning period. And of all things, the file you used for the mail merge was the one that didn't reflect it.
There was no malice. In fact, you intended to be polite. Yet, it arrived in the most disrespectful way possible. In offices where the lists aren't consolidated into one, this kind of accident is bound to happen every few years. In my experience, it was by no means a rare occurrence.
◆ Why do lists multiply into three?
To begin with, why do lists end up being split?
Usually, they grow the same way. The "Election" list, made in a hurry during the first campaign when there was no time. The "Supporter Association" list, tidied up after getting elected and things settled down. And the "New Year's Card" list, pulled out at the end of the year. Each one was created for a specific, valid reason at the time.
The trouble is that all three are "partially correct." The New Year's card list has the newest addresses because you've been correcting them every time mail is returned. The election list has mobile numbers that aren't on the supporter association list. The membership fee list still has a landline number that is no longer in use. The same person is listed on another line under their maiden name before marriage.
If any one of them were the "correct answer," it would be simple. But in reality, the correct answer is split and scattered across three files. That is why consolidation feels so difficult.
◆ The list grows old while you say "I'll do it later"
When you have three, updates stop.
Even when you hear new contact information, you don't know which file to write it in. It's a hassle to copy it into all of them. So, you scribble it on a notepad for now and leave it at that. "I'll combine them into one someday and fix it properly then"—half a year, a year passes since you thought that.
If a list isn't updated, it quietly grows old. It looks like it's alive, but the content slowly drifts away from reality. People who moved, people who passed away, people who stopped supporting, people who started supporting—without any of that reflected, the list remains frozen in last year's state.
◆ The truly painful part isn't that there are three Excel files
If you have read this far and found yourself nodding along, please let me share a more in-depth perspective.
The effort of merging three Excel files into one is, honestly, not a major problem. It can be finished in half a day. What is truly painful is what lies beneath that.
When you cannot trust your list, a sense of guilt—that you no longer have a grasp on your own supporters—gradually accumulates. What did I promise that person? What did I talk about with that family? The faces of each individual and the promises made to them slowly blur, just like the fragmentation of your files.
I believe the fragmentation of your list is a mirror reflecting the fragmentation of your relationships with your supporters. What you want to clean up is likely not the Excel files, but this sense of guilt.
◆ You don't need to aim for perfection. Just start by "gathering them into one"
So, please don't brace yourself. You don't need to create a perfect list right away.
There is only one principle for merging lists: before you try to make it clean, first "gather" everything into one sheet. You can calmly handle deletions and organization after you have finished gathering them. In fact, you will fail if you try to delete things right away.
◆ There are tools that can handle the judgment of duplicates for you
That said, to be honest, the subsequent task of "marking duplicates"—deciding for yourself which one to keep when the same person appears two or three times—is the most grueling part. I have seen many offices where work stops here, and the integration project gets pushed back to "later."
The service we are building, "Sentai," is designed for local assembly members and prospective candidates to consolidate their supporter lists in the cloud. When you import Excel or CSV files, it automatically identifies potential duplicates, allowing you to confirm "new, update, or skip" before finalizing. After integration, resident consultations and interaction history are linked to that person's record, allowing you to grow it from a mere "box for contact information" into a "list that tells you who you spoke with, what you discussed, and what needs to be reported."
▶ View Sentai's list features
Sentai List Features
However, there are surely many who are resistant to introducing a tool right away, or who want to clean up their current three files with their own hands first. For those people, I will write down the procedure for doing it using only Excel.
【Step 1】 Copy the originals (this is your lifeline)
Copy all three files and put them into a folder named "Pre-integration Backup." Always perform the work on the copied versions. Do not touch the original files, no matter what. This extra step prevents later regrets.
【Step 2】 Align only the column names
If you compare the first rows of the three files, the labels like "Full Name," "Name," and "Name (Honorific)" will likely be inconsistent. Align these. At a minimum, having the following five is sufficient.
・Full Name
・Furigana
・Address
・Phone Number
・Source (a new column to be created this time; the content can be left blank)
【Step 3】 Connect them vertically and write the "Source"
Paste the three aligned tables into one new file, one after another. At this time, write in the "Source" column whether that row came from the "Election List," "Supporter Association List," or "New Year's Card List." The same person will appear two or three times, but that is correct. Do not delete them yet.
【Step 4】 Mark the duplicates (deleting comes last)
If you set Excel's "Conditional Formatting" to "Duplicate Values" for the name column, names that appear two or more times will be highlighted. Simply look through the highlighted rows from the top, decide which one to keep, and mark the rows to be deleted as "Done." The criteria for judgment are, in order: 1) keep the row with a phone number, 2) keep the row with the newer address, and 3) if you are unsure, consolidate information from both rows into one.
After you have finished reviewing everything, delete them all at once at the end. It is safer to make another copy of the file right before you delete anything.
Three common pitfalls
・Two entries due to a maiden name... They look like different people if you only look at the name. You can notice this by checking the furigana and address.
・Only the address is new and the phone number is old... Be aware that "which column is correct" differs from file to file.
・Treated as different people due to mobile vs. landline... If you search for duplicates using only the phone number, these two will never be merged.
◆ Frequently Asked Questions
Q. Up to how many entries is it okay to keep using Excel?
A. In my experience, 500 entries is a good benchmark. Beyond that, checking for duplicates and organizing households becomes difficult to manage manually.
Q. Is it okay to have a family member help me?
A. The work itself is fine. However, since the list is full of personal information, it is safer to work together in front of the same computer rather than passing a working copy via USB or email.
Q. How long does it take to merge them?
A. For 3 files totaling 800 rows, it takes a little less than half a day even for someone who is not used to it. It is easier on the mind if you split the work, doing up to step 3 (just gathering) on the first day and marking the rows on a different day.
――――――――――
If you have read this far and feel like "I'll try cleaning it up in Excel first," that is perfectly fine. On the other hand, if you feel that handling duplicate checks and future updates alone is too much, you can try importing your current Excel file into Sentai. You can proceed while keeping your original data, and if it doesn't fit your needs, you can export the list as a CSV.
▶ View features, security design, and pricing
Sentai Features & Pricing Page
▶ Start for free until the end of August 2026
Start managing your list for free
Registration takes about 3 minutes. There is 0 yen initial cost, no credit card registration required, and it is 5,000 yen (excluding tax) per month including all features.
#LocalPolitician #PoliticalActivities #SupporterAssociation #ListManagement #Excel