Grouping users by three fields

Question

Hi!

I need help with the following:

I want to group the user list by employees. Here is an example data.

IDShortnameEmailOutput123asmithadam.smith@gmail.com1123asmith1adam.smith1@gmail.com1234asmithadam.smith1@gmail.com1567asmith2adam.smith@gmail.com1123asmith2adam.smith3@gmail.com1987dlopezdan.lopez@gmail.com2

I know that 5 first records belong to one employee (by ID, Shortname or email). My expected output is in the "Output" column. My goal is to get information which IDs, Shortnames and emails are associated with the same employee (here in the example noted as "1").

Thank you everyone for help:)

DanM · Answer

@nikadomi ,

I would suggest starting with with identifying which entries are unique. In your sample, ID 987 Shortname Dlopez etc is unique, which now lowers the amount of data you are working with.

The next step I would identify how many ID's show more than once. You can use the Summarize tool with a group by ID and a count of the ID. Once you have identified all of the ID's that show more than once, I would filter those out and sort. Make sure that the IDs are the same person.

You've now eliminated the one's that are easy to identify. So at this point based on your sample data, you would end up with ID 234 and 567 that are not in a group.  From here, you could clean up the Shortname by removing #'s and characters and attempt to use the join tool  against the shortname from the last step to see what matches you get in the join. If you cleaned up the Shortname for 234 and 567 and join it, it would join to the first entry giving you the ID 123 which would confirm they are the same person. You could then join this back to the original data set and update the ID field.

Hope that gives you an idea

DanM

Once you have the data cleaned and somewhat matching in the three areas