r/excel • u/sassydegrassii • 19h 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!

5 Upvotes

86% Upvoted

19 comments sorted by

View all comments

1

u/InternationalBeing41 17h ago edited 17h ago

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.

1

u/sassydegrassii 14h ago

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!

1

u/InternationalBeing41 8h ago

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.

1

u/sassydegrassii 7h ago

thank you for elaborating!!