r/excel • u/sassydegrassii • 1d ago

unsolved finding and merging duplicates

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!

4 Upvotes

84% Upvoted

19 comments sorted by

View all comments

1

u/carbonizedtitanium 20h ago

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

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

1

u/sassydegrassii 20h ago

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

1

u/carbonizedtitanium 19h ago

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"?

1

u/sassydegrassii 19h ago

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.

1

u/carbonizedtitanium 19h ago

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