Forum Discussion
People data lookups
Hi,
I have created an MS Form where anyone can nominate their peers for recognition - we have 20k employees. I've added a field in the form for a unique employee number for the nominator to include. So they would submit the nominee's: emp number, full name, role, area of the business. The output comes to me in excel and I need to update a list of 00's of employees, where they work, their contact details, line manager detail etc.
The challenge is this: whilst I can do all the look-ups to the external data to provide this, the output of the form could have numerous errors. First of all, a missing emp number or incorrect emp no. Second of all, incorrect name spelling, or shortened names, or duplicate names, or too many spaces etc. Then there's incorrect role or missing role, and finally the same for area of the business. Using either multiple IFs or look-ups or Power Query, what's the best solution to pull through the correct emp number (from my external data), when those first four fields could be all wrong, partly wrong, missing or all correct, and what about dealing with duplicate names? I can concatenate if the four fields are correct, but this isn't reliable. Is there a fuzzy match that takes the best guess at the four fields completed - leaving me to just check a handful of #N/As? I have Copilot and licence too. Thanks!
2 Replies
Can you please attach a sample file or share via OneDrive, Google-Drive, Dropbox or similar service so that we can check your data structure and type and suggest best formula for you?
You can do various things like use String (and RegEx) functions to check if the strings match or if the first x characters match and such but because of names being things like Robert or Bob, that could be a problem. You mentioned Copilot and that is sor tof the way I was thinking is you could ask AI to give you best match and then double check that list. You could also do a hybrid approach where maybe you first check if maybe both the emplyee number and last name match and if so just use that info but if not then ask copilot to try a match. I don't have and haven't used copilot but something like this should get you started:
=IF(last_name = XLOOKUP(emp_id, db[ids], db[last], ""), emp_id, COPILOT("Find best match for Employee ID " & emp_id &" and last name " & last_name & " and first name " & first_name " in this data set", db) )
where last_name, emp_id, and first_name are the entries on that line and the db is a table of all the emplyee information with db[ids] being the emplyee ID column and db[last] being the emplyee Last Name column.
