How to consolidate a directory. Fixing an Excel file where the same person appears three times
When I searched for "Nakamura" in the newly merged directory, three rows appeared.
All of them were "Koichi Nakamura." Two rows had the same address, while one row had a different house number. As for the phone numbers, the first row was a landline, the second was a mobile phone, and the third was blank.
How many of these rows actually belong to that Mr. Nakamura?
I stared at the screen for a while, my hands frozen.
"Teacher, I received two of these."
It was a phone call a few days after I had sent out the general meeting notices.
He laughed and asked, "Do you want two membership fees, too?" and I was able to get by with, "I'm so sorry, that was our mistake." I thought to myself how lucky I was that he was someone who could laugh about it.
However, something remained after I hung up the phone.
It wasn't about the cost of the stamps.
Sending two letters to the same person means that, according to our records, that person is counted as two people.
Someone who has supported us for so long was not being counted as one person by us. That fact had arrived in the form of two envelopes.
After that, I opened the directory and searched for "Nakamura." There were three rows.
I tried it again with another name. Sure enough, there were two rows.
I highlighted them. But that's where I stopped.
Marking the duplicate rows is relatively easy.
If you use Excel's conditional formatting, you can highlight cells that contain the same name. Depending on the directory, hundreds of rows might be highlighted as potential duplicates.
The problem is what comes next.
I look at the highlighted rows from the top, and by the tenth row, my hands stop. I leave the mouse cursor over the row number and can't move it.
What if these are actually different people?
All Excel tells you is that there is "the same character string."
It won't judge whether they are the 'same person' for you.
Same name, parent and child, maiden name. There are reasons for having the same name.
In local directories, you may find dozens of people with the same surname listed.
There are people with the same full name. There are people with similar names who are parent and child. There are also people who have changed their surname after marriage and are listed in both their maiden home's directory and their new family's directory.
The problem isn't that the task of deleting them is tedious.
The problem is that you don't know if it's okay to delete them.
The memory of having done it once
This is a story from an office that actually consulted me.
There were two rows for 'Kazuko Yamamoto' and 'Kazuyo Yamamoto' with the same address and same landline number. They were one character apart.
I judged it to be an input error and deleted one of them.
However, the two were a daughter-in-law and mother-in-law.
I realized this when only one attendance postcard for the general meeting arrived the following year. Even though they had always come together, they had become one person on the directory.
Once something like this happens, you hesitate to act next time.
I think that is only natural.
And then, you stop opening the directory.
When you open the directory, your eyes catch the highlighted duplicate candidates.
When you see them, you remember that they haven't been cleaned up. So, you gradually stop opening the directory.
You write new contact information in your smartphone notes. You put business cards in your desk drawer. You enter returned New Year's cards into a separate Excel file.
Information becomes scattered, and the directory drifts further away from reality once again.
Since it's not a job with a deadline, no one will get angry at you.
The next election is approaching, and the directory is still in last year's state, with no one complaining.
The real problem is not that the directory is messy.
This is the most important point I want to convey in this article.
The reason work stops is not because the directory is messy, your personality, or because you are bad at Excel.
It is because you are trying to make decisions on the spot, line by line.
You are making hundreds of consecutive decisions without established criteria. It is only natural that your standards will waver along the way.
Around the 50th row, you start to worry, "Was it really okay to merge them under these conditions?"
By the 100th row, you lose track of how you decided on the previous person.
That is when you close Excel.
Therefore, let's reverse the order.
Consolidation is not the task of grouping rows that seem to be the same person based on intuition.
It is the task of first deciding on "conditions that allow you to say they are the same person," and then grouping only the rows that meet those conditions.
Do not group rows that lack sufficient conditions.
Leave them as "Needs Verification." That is perfectly fine.
Do not try to create a perfect directory all at once.
What you should aim for first is reducing the accident of sending duplicate invitations to the same person.
Before the steps, a little bit about our tools.
I will explain all the specific steps in Excel after this.
Before that, just one thing.
We are building a cloud workspace for local assembly members and prospective candidates. It is called **sentai**.
It is a service like an office on the internet that connects and manages supporter lists, records of resident consultations, parliamentary preparations, and drafts for public relations.
This article relates to the directory import feature.
When you import an Excel or CSV file you have on hand, the results of matching against the already registered directory are displayed line by line before you confirm the import.
Rows with sufficient matching information are candidates for "Update"
Rows where no matching person is found are candidates for "New"
Rows where it is difficult to determine if it is the same person are left for confirmation rather than being decided automatically
If it is a different person, change it to "Skip"
The judgment can be changed manually for each row. It is a system where you confirm the content and then confirm the import in bulk.
To be honest, I will also write about what it cannot do.
Rows with empty phone numbers or email addresses, rows containing old surnames, or rows with significantly different spellings cannot be fully captured by machine judgment alone.
It is not a "magic tool that cleans everything up," but rather closer to a tool to reduce the number of rows that people need to check.
The imported directory can be exported in CSV format when needed and opened in Excel.
▶ See the Sentai directory feature
Procedure for consolidating using only Excel
From here on, this is a method to proceed using only Excel without installing new tools.
The important thing is not to start deleting immediately.
Preparation | Create a working copy and 6 columns
First, copy the original Excel file and save it under a different name.
Do not touch the original file.
The working file contains personal information. Check the save location and sharing settings, and make sure not to place it in a location where unrelated people can access it.
Next, add the following 6 columns to the right end of the directory.
First, assign a permanent management ID
Row numbers change when you sort the data.
Therefore, if you record by row number, such as "Consolidated -> Row 28", you may not be able to reference it later.
Please assign a permanent management ID to each row.
If the first row is a header and the data starts from the second row, enter the following formula in the second row of the management ID column.
="M"&TEXT(ROW()-1,"0000")Copying it to the bottom will assign numbers like M0001 and M0002.
Once you have finished assigning numbers, copy the management ID column and fix it by using "Paste Values".
If you leave it as a formula, the numbers will change when you sort the data.
Step 1 | Gather potential duplicates by phone number
The first thing to look at is the phone number.
However, just because phone numbers match does not mean they are immediately the same person.
This is because spouses or parents and children may use the same landline.
Use the phone number as a powerful clue to group rows that might be the same person together.
Remove spaces and symbols from phone numbers
If the phone number is in column D, enter the following formula in the second row of the "Key Phone" column.
=IF(D2="","",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2,"-",""),"－",""),"ー","")," ","")," ",""),"(",""),")",""),"（",""),"）",""))This formula removes the following characters.
Half-width hyphen
Full-width hyphen
Prolonged sound mark
Half-width space
Full-width space
Full-width and half-width parentheses
With this, you can compare the following phone numbers as the same value.
090-1234-5678
090 1234 5678
09012345678
If other symbols are mixed in, remove them using 'Find and Replace' with Ctrl+H, or add a SUBSTITUTE function.
Counting how many entries have the same number
If 'Key Phone' is in column N and 'Phone Duplicate Count' is in column O, enter the following formula into O2.
=IF(N2="","",COUNTIF($N$2:$N$1200,N2))Please read 1200 as a number slightly larger than the last row of your directory.
The initial IF is important.
Without this, rows with blank phone numbers might be treated as the same group.
Once you copy the formula to the bottom, filter for rows where 'Phone Duplicate Count' is 2 or more, and sort by 'Key Phone'.
Rows with the same phone number will be lined up next to each other.
Judging when phone numbers match
Make judgments as follows.
Matching phone numbers is for finding candidates for consolidation.
Do not delete rows based solely on the phone number.
When the leading '0' has disappeared
If you enter phone numbers as numbers, Excel may delete the leading zero.
Before entering or pasting phone numbers, select the original phone number column and set the cell format to "Text".
If 09012345678 has already become 9012345678, changing the format to text afterward will not restore the missing zero.
In that case, check the original directory, business cards, or contact information to restore it.
Step 2 | Collect duplicate candidates using furigana
For rows that could not be picked up by phone number, look at them next using furigana.
In kanji, "Watanabe" (渡辺), "Watanabe" (渡邊), and "Watanabe" (渡邉) are different strings, but if the furigana is entered correctly, they can all be compared as "Watanabe" (ワタナベ).
If the furigana is in column B and the "key kana" is in column P, enter the following formula in P2.
=IF(B2="","",SUBSTITUTE(SUBSTITUTE(B2," ","")," ",""))This is a formula to remove half-width and full-width spaces.
Next, if the "kana duplicate count" is in column Q, enter the following formula in Q2.
=IF(P2="","",COUNTIF($P$2:$P$1200,P2))Copy the formula to the bottom and filter for rows where the "kana duplicate count" is 2 or more.
What you can and cannot tell from furigana
Using the furigana for the entire name makes it easier to find variant characters and variations in kanji spelling.
However, the furigana for the entire name will differ between a maiden name and a current surname.
For example, "Tanaka Hanako" and "Yamada Hanako" will not match using this method.
For rows that appear to be maiden names, please verify them manually by combining the first name, phone number, address, affiliation, and past records.
If there are many rows where the furigana is blank, you can skip this step.
If you try to "fill in the furigana for everyone first," your work will stall there.
Step 3 | Use addresses as a final verification tool
Look at the rows remaining up to this point using the address.
However, an address alone is not a deciding factor.
"1-2-3" and "1-chome 2-3" become different strings
Couples and parents/children share the same address
People who have moved have both old and new addresses remaining
Sometimes both office and home addresses are registered
Use the address as supplementary material to verify two rows that might be the same person.
Do not assume that "same address means same person."
How to decide which row to keep
Once you have confirmed it is the same person, decide which row to keep.
The priority order is as follows:
The row containing information recently confirmed by the person or their family
The row containing a currently reachable phone number or email address
The row containing the current address including the house number
The row with more information such as affiliation, referrer, or consultation history
Use the most recent and reliable row as the foundation.
And what is important is to transfer information from the row to be deleted to the row to be kept before merging.
If the row to be kept has a mobile phone number and the row to be deleted has a workplace or referrer, copy that information to the row to be kept.
If you delete without doing this, you will later find yourself saying, "It should have been written somewhere."
Do not delete immediately. Record as "Merged"
Even after verification is complete, do not delete the row immediately.
Record the following in the "Judgment" column.
Consolidation candidate
Different person
Needs verification
Consolidated → M0123
M0123 is the management ID where the information has been consolidated.
Rows marked as "Consolidated" will be excluded from invitation letters and address printing.
While it may look a bit complex, you will be able to verify later why this person was combined into one row.
There is plenty of time to delete them completely after you have finished verifying the operation and creating a backup.
Finally, perform a spot check on 10 entries
Once you have finished the work, search for and verify at least 10 entries.
Select 5 supporters you know well and check if any unnecessary duplicates remain
Select 3 people from an area with many common surnames and check if family members have been incorrectly combined into one
Select 2 people who have recently changed their address or phone number and check if the new information has been retained
Checking 10 entries does not guarantee that there are no mistakes at all.
However, if the judgment rules themselves are incorrect, there is a high possibility that you will notice it here.
Before printing the invitation letters, please check once more to ensure there are no duplicates in the names and addresses of the output list.
Common pitfalls
Couples or parents and children with the same address and landline number
Even if the surname, address, and phone number are all the same, they are different people if their given names are different.
Please make sure not to combine family members into one entry.
Mobile and landline numbers are on separate rows
Even for the same person, one row might only contain a mobile number, while another only contains a landline number.
Since phone numbers alone are not enough to match them, verify using phonetic readings, addresses, affiliations, etc.
Corporate and individual entries are mixed
Do not immediately merge entries just because you see both "XX Construction Taro Yamada" and "Taro Yamada".
There are cases where you should send separate invitations to a company address and a home address.
Make a decision after confirming the purpose of the mailing.
Maiden names and current surnames are mixed
Verify by combining information such as phone numbers, email addresses, first names, and past addresses.
You cannot determine if they are different people or the same person just because the surnames are different.
Frequently Asked Questions
Q. At what number of entries does manual processing become difficult?
It cannot be determined by a fixed number of entries.
Even if there are many entries, if phone numbers and phonetic readings are consistent, it is relatively easy to narrow down candidates.
Conversely, even with only a few hundred entries, if there are many blank fields and the list is being updated by multiple people separately, it takes time to make a judgment.
If the following situations persist, it is a sign to consider tools that can check for duplicates during import.
You repeat the same consolidation work every year
Multiple people are updating separate Excel files
Duplicates increase every time you add to the list
You do not know which file is the latest
The reasoning behind the decision was not recorded
Q. During the consolidation process, I found someone who has passed away. Should I delete the row?
I recommend keeping the status as "Deceased" or "Stop Mailing" instead of deleting it.
If you delete it, there is a possibility that the same person's information will be imported again from another directory next year.
Please manage your operations so that rows marked "Deceased" or "Stop Mailing" are excluded when sending out invitations or performing mail merges.
Let's return to the three rows for "Nakamura" at the beginning.
At the office I helped back then, when we sorted by phone number, we found that two of the three rows had the same mobile phone number.
The remaining row had a different sub-number for the address and the phone number field was blank.
Upon checking, it turned out to be the son.
The three rows became two rows.
Not one row.
That was the correct result.
Consolidation is not a task for shortening a directory.
It is a task to reach a state where you can say with evidence, "This row and this row are the same person."
You can leave entries that you cannot confirm as "Needs Verification."
For now, just the phone number column is fine.
Try taking just 30 minutes to start by checking how many potential duplicates there are.
▶ See Sentai's directory features, security design, and pricing
▶ Start for free until the end of August 2026
Registration takes about 3 minutes. No initial fees, no credit card required. All features included for 5,000 yen (excluding tax) per month.
*Fees, free trial periods, and service specifications are current as of August 14, 2026.
Sources and references
#LocalCouncilors #CouncilorActivities #SupportAssociation #ListManagement #Excel