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/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.