将多个联系人链接到Google表格或R中的单个ID [英] Linking several contacts to a single ID in Google Sheets or R

问题描述

更新我添加了指向Google表格的链接，并且列ID想知道如何创建

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).

第二张纸上将包含联系人标识符(唯一ID)，以及他们的姓名，电话，电子邮件和其他联系人信息.

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

我对R和google工作表都很熟悉，因此可以采用解决方案在这两个方法中进行创建.或者，如果有帮助，我可以将它们导入到Airtable中.

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.

推荐答案

=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) ))

这篇关于将多个联系人链接到Google表格或R中的单个ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！