Excel:将重复的电子邮件数据与最新日期合并

Excel:将重复的电子邮件数据与最新日期合并

我有上千个电子邮件列表,其中包含订阅结束日期和联系方式。每行都没有完整的数据。例如第 2 行[电子邮件保护]没有联系方式,但第 3 行[电子邮件保护]有联系人。我如何将它们排序到正确的表格中,如下图所示?

在此处输入图片描述

答案1

D2将把这个数组公式(使用Ctrl+Shift+Enter):

=INDEX($B$2:$B$7,MATCH(1,($A$2:$A$7=A2)*($B$2:$B$7<>""),0))

在此处输入图片描述

E2这个常规公式中:

=MAXIFS(C:C,A:A,A2)

或者这个数组公式(使用Ctrl+Shift+Enter):

=MAX(IF($A$2:$A$7=A2,$C$2:$C$7,0))

在此处输入图片描述

答案2

如果您拥有具有、和函数的 Office 365 FILTERUNIQUETEXTJOIN可以使用以下公式:

请注意,我使用了带有结构化引用的表,但如果您愿意,可以将其转换为正常寻址

email:    =SORT(UNIQUE(Table1[email]))
Contact:  =TEXTJOIN(", ",TRUE,UNIQUE(FILTER(Table1[Contact],(Table1[email]=I2))))
expired:  =MAXIFS(Table1[expired],Table1[email],I2)

email公式将SPILL向下。其他公式必须填充/向下拖。

Contact如果多个联系人不同,则会将它们连接起来,否则只返回一个联系人。

在此处输入图片描述

如果您没有这些功能,您可以使用Power QueryWindows Excel 2010+ 和 Office 365 Excel 中提供的 获取所需的输出。

你不得不刷新如果数据表中进行了更改,则查询。

  • 选择原始表格中的某个单元格
  • Data => Get&Transform => From Table/Range
  • 当 PQ UI 打开时,导航至Home => Advanced Editor
  • 记下代码第 2 行的表名称。
  • 用以下代码替换现有代码M 代码以下
  • 将粘贴代码第 2 行的表名更改为您的“真实”表名
  • 检查任何注释以及窗口Applied Steps,以更好地理解算法和步骤

M 代码

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"email", type text}, {"Contact", type text}, {"expired", type date}}),

//Group by Email
//  "Contact" line not available from the UI
//  If there are multiple, **different** contacts, they will all be returned
//    otherwise just the unique contact will be returned
    #"Grouped Rows" = Table.Group(#"Changed Type", {"email"}, {
        {"Contact", each Text.Combine(List.Distinct([Contact]),", ")},
        {"Expired", each List.Max([expired]), type nullable date}       
        })
in
    #"Grouped Rows"

在此处输入图片描述

答案3

上述解决方案适用于定期进行广泛的过滤。对于简单的解决方案,我会进行多列排序,首先按电子邮件排序,然后按到期时间排序(升序)。然后创建一个带有公式的“最新联系人”列

=IF(B2="",B1,B2)

然后创建一个“最新到期日”列

=IF(C2="",C1,C2)

然后创建“重复”列

=IF(A2=A3,"duplicate","final")

现在过滤所有“最终”行,然后根据需要复制并粘贴到新工作表中。

相关内容