Question

在下面的屏幕上,我正试图说明我如何能够把与一家公司相关的所有电子邮件汇合在一起。我已尝试过TEXTJOIN,但可以说明这项工作的逻辑。我也愿意接受任何世行解决办法。

Compnay Name	Billing Account Number	Sales Account Number	Email Address	Output
Blue Bonet	1	1ABC	[email protected]	[email protected]; [email protected]
Blue Bonet	1	1ABC	[email protected]
Disney	2	2ABC	[email protected]	[email protected]; [email protected]; [email protected]; [email protected]
Disney	2	2ABC	[email protected]
Disney	2	2ABC	[email protected]
Disney	2	2ABC	[email protected]
Berkshire	3	3ABC	[email protected]	[email protected]
Mia s Pizza	4	4ABC	[email protected]	[email protected]; [email protected]; [email protected]
Mia s Pizza	4	4ABC	[email protected]
Mia s Pizza	4	4ABC	[email protected]
Jerry s Barber Sho	5	5ABC	[email protected]	[email protected]
Tom s Dinner	6	6ABC	[email protected]	[email protected]; [email protected]; Norwood.O [email protected]
Tom s Dinner	6	6ABC	[email protected]
Tom s Dinner	6	6ABC	Norwood.O [email protected]
Gilberts	7	7ABC	[email protected]	[email protected]; [email protected]; [email protected]; [email protected]
Gilberts	7	7ABC	[email protected]
Gilberts	7	7ABC	[email protected]
Gilberts	7	7ABC	[email protected]

Answer 1

这里是使用()进行这项工作的一种方式。

=MAP(A2:A19,LAMBDA(α,IF(ROW(α)=XMATCH(α,A:A),TEXTJOIN("; ",,FILTER(D:D,A:A=α)),"")))

然而,如果你想要缩小公式,上述公式就会消失:

=TEXTJOIN("; ",,FILTER($D$2:$D$19,(A2=$A$2:$A$19)*(COUNTIF(A$2:A2,A2)=1),""))

Answer 2

Load data into an array, group Email with a Dict object

Option Explicit
Sub Demo()
    Dim objDic As Object, rngData As Range
    Dim i As Long, sKey As String, colCell As Collection
    Dim arrData, arrRes, RowCnt As Long
    Const KEY_COL = 2     group by `Billing Account Number`
    Const DATA_COL = 4    concate Email
    Const DEST_COL = 5    output to Col E
    Set colCell = New Collection
    Set objDic = CreateObject("scripting.dictionary")
      load data into array
    Set rngData = Range("A1").CurrentRegion
    arrData = rngData.Value
    RowCnt = UBound(arrData)
      loop through data
    For i = LBound(arrData) + 1 To RowCnt
        sKey = arrData(i, KEY_COL)
        If objDic.exists(sKey) Then
            objDic(sKey) = objDic(sKey) & "; " & arrData(i, DATA_COL)
        Else
            objDic(sKey) = arrData(i, DATA_COL)
            colCell.Add i
        End If
    Next i
      populate output array
    ReDim arrRes(1 To RowCnt, 0)
    arrRes(1, 0) = "Output"
    For i = 1 To objDic.Count
        arrRes(colCell(i), 0) = objDic.items()(i - 1)
    Next
      write output to sheet
    Cells(1, DEST_COL).Resize(RowCnt, 1).Value = arrRes
End Sub

友情链接