CODEWITHSUNDEEP
google-sheets
tom
tom

Reputation: 1077

Linking several contacts to a single ID in Google Sheets or R

Update I added a link to a Google sheet and the column id like to know how to create

https://docs.google.com/spreadsheets/d/1oIqPf1OffpeCeMJW6kMJztT3mAWCLBPE7f8Ohr066wo/edit?usp=sharing

I need to analyze communications between different team members and users and need a way to combine different contact methods into a single identifier. I have a sheet that lists all the different contact methods for a single person (email, slack, phone), but not a way to link that to the sheet that keeps track of communication. Here's how the problem is set up.

The first sheet has the method of contact for each row. So, if the same person was contacted once by email and then by phone, the email would be in one row and the phone number would be in the second row (both listed in the same column).

The second sheet would have the contacts identifier (a unique ID), along with their name, phone, email, and other contact info.

How would I create a column in the first sheet that lists the unique identifier associated with any type of contact to that person?

So, for instance, the ideal solution would have something like this for hypothetical two people (the third row represents another person, the first two rows are the same person).

Col1          ID
email@name    4
555-555-5555  4
SecondPerson  5

I'm familiar with both R and google sheets, so can take solutions to create this in either. Or, I can import them into an Airtable if that helps.

I tried to look up solutions for record linking, but since all of the contact types are listed in the same column, I couldn't find a solution. Thanks and let me know if I can make the question more clear.

Upvotes: 0

Views: 103

Answers (1)

player0
player0

Reputation: 1

=ARRAYFORMULA(IFERROR(VLOOKUP(B2:B, {contacts!B2:B,contacts!A2:A;
                                     contacts!C2:C,contacts!A2:A;
                                     contacts!D2:D,contacts!A2:A}, 2, 0) ))

0

Upvotes: 1

Related Questions