1

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.

Improve this question
1
  • Welcome. I am afraid we will need more info than what you provided so far. Please read how to and share a test sheet so as you can be easier helped.
    – marikamitsos
    Oct 13, 2020 at 19:02

2 Answers 2

Reset to default
0

@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:

=SORT(UNIQUE('Employee Data'!I8:J95), 1, TRUE)

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.

Improve this answer
6
  • I did try that and it did pull the data along -- yay --, except I should have used a better hypothetical example, because I & J are next to each other and the data I want is several columns over -- pretend its column P. I don't know how to pull a noncontiguous column of data over.
    – M. Schultz
    Oct 14, 2020 at 20:44
  • Column P is one column. Can you be more specific about which exact columns and ranges you are trying to work with?
    – Erik Tyler
    Oct 15, 2020 at 21:29
  • The (name) data is in column I and the phone number data in in columns L, M & N. Columns J & K are the name in column I, which I use split to extract the first and last name because sometimes I just want the staff member's first name. Some of the names repeat, so I use the sort unique to get an alpha sorted list and that works; I just have no idea how to pull along associated row data for the staff member. I'm a school secretary with little experience in this, so in retrospect, I think I need to redesign the initial employee data sheet that all other sheets work off of, sigh.
    – M. Schultz
    Oct 16, 2020 at 22:11
  • This still does not make sense to me: "the phone number data [is] in columns L, M & N." Remember, I/we can't see what you see. As such, it doesn't make sense how a phone number is split up between three columns. That said, to pull non-continuous columns, contain them between curly brackets and separate them with commas like this: =SORT(UNIQUE({'Employee Data'!I8:I95,'Employee Data'!IL:L95}), 1, TRUE)
    – Erik Tyler
    Oct 17, 2020 at 23:12
  • It makes sense if you think of the phone # split into Area Code, Exchange, and Last 4 digits. (888,555,1212) OR country code, area code, last 7 digits (1, 888, 5551212) . Then you could sort the list by country or area code if you have employees across the country or international. Just a thought.
    – ArchonOSX
    Oct 18, 2020 at 8:30
0

Additional comments point toward wanting to know how to retrieve combinations of non-contiguous columns. There are many ways to do this. However, the one closest to the original formula in the post above (which I assume is most familiar to you, M. Schultz) would be to build a virtual array by containing the columns between curly brackets separated by a comma (or commas if more than two):

=SORT(UNIQUE( {'Employee Data'!I8:I95 , 'Employee Data'!L8:L95} ), 1, TRUE)
Improve this answer
4
  • Sorry for the delay in thanking you for thi! I tried as you suggested and I got a error message --> Function ARRAY_ROW parameter 2 has mismatched row size. Expected: 88. Actual: 784. Oh how I wish I was smarter...
    – M. Schultz
    Dec 17, 2020 at 0:58
  • Omg even a blind squirrel can find a nut. Thanks to you, I tried this and it's working. =SORT(UNIQUE( {'Employee Data'!I8:I95 , 'Employee Data'!L8:L95 , 'Employee Data'!M8:M95 , 'Employee Data'!N8:N95 , 'Employee Data'!O8:O95} ), 1, TRUE)
    – M. Schultz
    Dec 17, 2020 at 1:08
  • Omg I'm so excited! Thank you thank you thank you.
    – M. Schultz
    Dec 17, 2020 at 1:17
  • Glad to have had a part in getting you where you were trying to go. As I often say, everyone essentially learns in the same way: from scratch and one piece at a time. So consider this a win. Keep learning. If you do, you'll look back in a while and realize that you know an awful lot.
    – Erik Tyler
    Dec 17, 2020 at 2:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.