Very inexperienced google sheets user here. I have a sheet with employee data. One row and many columns of data for each employee. Some employees appear more than once (i.e. in multiple rows)
I need a sheet with a list of employee names and phone numbers. I am using the following formula to find, remove duplicates, and alpha sort their names. This seems to work. I end up with an alpha sorted list of employees.
=SORT(UNIQUE('Employee Data'!I8:I95), 1, TRUE)
If their phone numbers are all in column J, how can I "drag" them along into my new sheet with this formula?
Thanks.
Best Answer
@M. Schultz, if every name in I:I has one and only one phone number in J:J (or never has a phone number in J:J for that name), then you can use this:
However, if you have cases where one person is listed several times with something different in J:J for the phone number, you have to think about which phone number to pair with the name. Otherwise, you'll still wind up with duplicates.
Let me illustrate.
Suppose you have the following I:J combos:
Sally Smith | 555-555-1212 Sally Smith | 555-444-1313 Sally Smith |
If you request UNIQUE of just I:I, you'll get "Sally Smith."
But if you request UNIQUE of I:J, you'll get all three of those rows returned as they were originally; because each combination is considered unique (i.e., A|1 A|2 A| ).
Often, if you have "Sally Smith" cases like I've shown above, and if you have a column with a timestamp of when the entry came in, you can write a formula that uses the date to retrieve the most recent name+phone combination for each person. But in order to help you do that, you'd need to share a link to your sheet (or a copy of your sheet, or a sheet containing an adequate "sanitized" data set from your sheet with everything exactly where it is in your original sheet but with personal data replaced with unique mock data).
If you do choose to share that link, be sure that when you set up the "Share" permission, you choose "Anyone with the link can edit."
Hope this helps.