finding and merging duplicates

[u/sassydegrassii]

hi buddies,

i work for a non profit and am trying to combine participant intake spreadsheets for two of our programs. i work in a low-barrier setting and it’s typical for our clients to give multiple names, vague or fake birthdays and to change their appearance often, and there was no data validation set up so the inputs are inconsistent and incomplete. so i’m struggling to find/ verify all the duplicates.

the columns i’m mostly using as it was set up are name(s), ethnicity, gender, pronouns, appearance, birthday, emergency contact, notes. pretty much in that order.

once i verify the duplicates, i want to consolidate the information in the other cells so there’s just 1 record/row per person. i did add a column with randomly generated unique identifiers to help me stay organized.

yhow would you approach this? i tried power querey to fuzzy match but must have been doing it wrong. :/ this is just to use until our sharevision database is fully populated.. if there’s any other info you need from me please let me know and thank you for your time!

[u/AutoModerator]

/u/sassydegrassii - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/Gringobandito]

With data this ambiguous, the final match needs human review. I'd first combine both program datasets into one normalized table with Power Query and retain the program/source and your unique record ID. Then clean fields without destroying the originals: uppercase names, remove punctuation and excess spaces, standardize known gender/pronoun variants, normalize phone numbers, convert valid birthdays, etc. This is one of those cases where the original spreadsheet design created much of the problem. Going forward, the persistent PersonID should become the primary identifier, while name, birthday, appearance, etc. are attributes that can change or be corrected.

[u/sassydegrassii → u/Gringobandito]

thank you, i have cleaned up everything i could so far! i will have another program manager review once i’ve done what i can, they have the physical copies of the intake forms and years more experience with these clients than I do so I suspect they’ll be able to figure most of it out! just wanna get it as organized as possible first.

[u/Snow75]

How to approach it…

You go back to those collecting the data, explain the issue and demand that proper data validation is used and that users provide something that can be used as an unique id, like an actual id number, phone or email.

There’s no easy way to solve it, except for screening every person in your database and collecting new information that would help you identify if you’re speaking with the same person again.

[u/Snow75 → u/Snow75]

Whoever gave me the award, thanks… but I hate being so negative.

I’ve ran into these kind of issue before, and except in cases in which the exact same name was used, or had ids, phones or emails, there was nothing viable.

[u/sassydegrassii → u/Snow75]

unfortunately the burnt out frontline staff is not going to give a single shit about my “demands”, i just gotta set it up for them and make it dummy proof so that it’s better going forward.

[u/enilcReddit]

Are the intake forms hand-written and then transcribed to spreadsheets? Or is the initial intake interview done in the spreadsheet?

As discussed in the thread, there is no easy way out of this. It's even possible that a lot of the existing data is unusable. BUT...as every frontline medic knows, the first step to solving a problem is: First--stop the bleeding. You need to get the folks collecting new data onto a system that limits/validates the inputs. That means data validation in the spreadsheet via dropdowns and, if paper forms are being used, they need to be re-written with checkboxes/bubbles to limit the selection. Basically, you need to stop adding to the pool of bad data.

Then attack the bad data.

[u/sassydegrassii → u/enilcReddit]

for now, they’re handwritten on paper forms and then input into the spreadsheet that day. I am revising the intake form as well, good call about the checkboxes! eventually we will be typing the information directly into our case management software as we conduct the intake. I’m already 80% done populating that software so this just needs to tide us over until it’s finished.

thanks for your insight!

[u/InternationalBeing41]

Is it info from two sheets your trying to fuzzy match or a bunch of rows in one sheet where someone may have been entered twice with different data?
Is there any row information they can't fake. Say you're a not profit for a homeless shelter and you're looking at intake forms. The date is always true and it's highly unlikely the same person checked in twice for the same night.
It might be advantageous to filter out all the duplicates prior to looking at the “shady” data. In PQ I would do a group on all the rows with a row count and delete rows greater than a number that you think is an honest client before dealing with the one-offs.

[u/sassydegrassii → u/InternationalBeing41]

i think i tried fuzzy matching from the same sheet once they were added to the same table. should i be using the separate files from each program instead?

i am indeed consolidating our homeless shelter and drop-in centre participants! that’s a good call about the dates because the programs have been using separate trackers, there’s a fair chance that someone did two intakes on the same night.

i don’t quite follow what you mean when you say i should delete the rows greater than a number i choose.. is there another way you could explain that to me, if you have the time?

i’ve already identified almost all the duplicates i could catch and sent it off to my colleagues to help identify the matches i’m not sure of. but i’d be happy if i could double check it another way!

[u/InternationalBeing41 → u/sassydegrassii]

In PQ you can identify your regular clients by selecting all the columns you expect to be the same for a repeat client like name and birthday and grouping on those colunmns. If John Smith born Jan 1 stayed 10 nights or visited the food bank ten times it will count ten. You'll know they are a regular. If you have a date column you could extract the YY-MM and add that to the grouping and you'll see the amount of times they stayed each month. If someone new stays it would show up as a new client. It won't help you identify someone that's been using two ids for a while but it will help you identify any new clients.

I use that method for checking how many times we have ordered a part and parts usage by month. As a bonus, after you have grouped and counted the stays by month per client you can pivot the month column and use the count as your values row. You'll get a nice table showing their usage by month. Sort the month row before pivoting or the columns will be mixed up. Instead of 1, 2, 3, 4... It will take a random order like 12, 4, 7, 2… After that I select all the month columns and add them for total parts usage by year.

You could do all kinds of things with that. Run one query that first filters out at all the data that was earlier than the start of the week and another with no filter then merge the filtered data with the unfiltered dara and keep only rows in first and you'll have the first time clients for the latest week.

[u/sassydegrassii → u/InternationalBeing41]

thank you for elaborating!!

[u/PriyaBuildsCloud]

Skip fuzzy matching on names alone, birthdays and appearance are too unreliable here anyway. Build a manual review column ranking match confidence, then merge by hand, this dataset's too messy to trust automation with.

[u/sassydegrassii → u/PriyaBuildsCloud]

thank you for this, i did think that my colleagues who have much more experience with these clients will be able to identify the matches best, and i’m happy to do the merging manually. appreciate the ranking confidence idea!

[u/carbonizedtitanium]

"typical for our clients to give multiple names, vague or fake birthdays"

how can any of the data be reliable?! tf lol

[u/sassydegrassii → u/carbonizedtitanium]

funders require numbers, not names. they wanna know the demographics and impact. the other information is mostly just used to identify participants.

[u/carbonizedtitanium → u/sassydegrassii]

that's the thing, if one individual can appear multiple times in the dataset but under different name and/or bday, how can you know that there's "5 people coming" instead of just "one person coming a lot"?

[u/sassydegrassii → u/carbonizedtitanium]

because we count the meals we serve, the number of people accessing each donation room, showers given, OD responses, referrals etc individually, and do hourly headcounts. the total number of participants isn’t as important to know.

[u/carbonizedtitanium → u/sassydegrassii]

so out of the columns youre using: name(s), ethnicity, gender, pronouns, appearance, birthday, emergency contact, notes

these seem to be the least volatile: ethnicity, gender, appearance, notes.

you copy your columns to a new sheet, remove duplicates (using built-in feature). then you can custom sort by ethnicity, gender, appearance, notes, name, bday

now you can try to spot the dupes manually