问题

（我的第一个StackOverflow帖子）

在Excel中，我正在为来自另一个系统导出的每个唯一ID的一堆“标签”执行文本连接，示例如下。目前，我必须更改TEXTJOIN公式中的范围，以适应给定ID发生的次数（即标签数量），但我希望能够简单地使用自动填充公式，只需双击即可填充下来，仅在第一次使用文本连接时使用唯一ID，以便我可以删除重复项并获得每个ID的所有标签。

数据示例:

目前，我必须拖动此公式=TEXTJOIN(";",TRUE;"A2:A5")，直到标签数量发生变化，然后编辑公式并再次拖动下来。我希望最终只有每个唯一ID的单一出现，带有所有相关的标签，以便我可以导入它。

答案1

得分: 3

请尝试以下公式-

=MAP(A2:A17,
LAMBDA(x,
IF(COUNTIFS(A2:INDEX(A:A,ROW(x)),x)=1,
TEXTJOIN(",",1,FILTER(B2:B17,A2:A17=x)),"")
))

=MAP(A2:A17,
LAMBDA(x,
IF(COUNTIFS(A2:INDEX(A:A,ROW(x)),x)=1,
TEXTJOIN(",",1,FILTER(B2:B17,A2:A17=x)),"")
))

答案2

得分: 3

以下是要翻译的内容：

"Using Excel Formulas:"

• Formula used in cell D2

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

The above formula needs to be filled down respectively in order to get the desired outputs.

"Using Power Query:"

To perform the above task using Power Query follow the steps:

• First convert the source range into a table and name it accordingly, for this example I have named it as Table1

• Next, open a blank query from Data Tab --> Get & Transform Data --> Get Data --> From Other Sources --> Blank Query

• The above lets the Power Query window opens, now from Home Tab --> Advanced Editor --> And paste the following by removing whatever you see, and press Done

M-Code:

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"TicketId"}, {{"All", each _, type table [TicketId=text, COUNTIF=text, Tag=text]}, {"Sum", each Text.Combine([Tag],"; "), type text}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([All],"Index",1,1)),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"COUNTIF", "Tag", "Index"}, {"COUNTIF", "Tag", "Index"}),
#"Added Conditional Column" = Table.AddColumn(#"Expanded Custom", "Result", each if [Index] = 1 then [Sum] else null),
#"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"All", "Sum", "Index"})
in
#"Removed Columns"

• Lastly, to import it back to Excel --> Click on Close & Load or Close & Load To --> The first one which clicked shall create a new sheet with the required output while the latter will prompt a window asking you where to place the result.

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

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"TicketId"}, {{"All", each _, type table [TicketId=text, COUNTIF=text, Tag=text]}, {"Sum", each Text.Combine([Tag],"; "), type text}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([All],"Index",1,1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"COUNTIF", "Tag", "Index"}, {"COUNTIF", "Tag", "Index"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded Custom", "Result", each if [Index] = 1 then [Sum] else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"All", "Sum", "Index"})
in
    #"Removed Columns"

答案3

得分: 3

另一个选择：

D2 中的公式：

=MAP(A2:A17,LAMBDA(s,IF(ROW(s)=XMATCH(s,A:A),TEXTJOIN(";",,FILTER(C:C,A:A=s)),"")))

PowerQuery:

为了好玩，我尝试了一种不分组的方法：

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Step1 = Table.TransformColumnTypes(Source,{{"TicketID", Int64.Type}, {"COUNTIF", type any}, {"Tag", type text}}),
    Step2 = Table.AddIndexColumn(Step1, "Index", 0, 1, Int64.Type),
    Step3 = Table.AddColumn(Step2, "Custom", each let l=Table.Column(Step2,"TicketID") in if List.Combine({{0},l}){[Index]}<>l{[Index]} then Text.Combine(List.Transform(List.Numbers(0,List.Count(l)),(n)=> if [TicketID]=l{n} then Table.Column(Step2,"Tag"){n} else null),";") else null),
    Step4 = Table.RemoveColumns(Step3,{"Index"})
in
    Step4

=MAP(A2:A17,LAMBDA(s,IF(ROW(s)=XMATCH(s,A:A),TEXTJOIN(";",,FILTER(C:C,A:A=s)),"")))

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Step1 = Table.TransformColumnTypes(Source,{{"TicketID", Int64.Type}, {"COUNTIF", type any}, {"Tag", type text}}),
    Step2 = Table.AddIndexColumn(Step1, "Index", 0, 1, Int64.Type),
    Step3 = Table.AddColumn(Step2, "Custom", each let l=Table.Column(Step2,"TicketID") in if List.Combine({{0},l}){[Index]}<>l{[Index]} then Text.Combine(List.Transform(List.Numbers(0,List.Count(l)),(n)=> if [TicketID]=l{n} then Table.Column(Step2,"Tag"){n} else null),";") else null),
    Step4 = Table.RemoveColumns(Step3,{"Index"})
in
    Step4

答案4

得分: 2

Sure, here's the translated content:

还有一个VBA解决方案（也已打上标签），构建一个仅包含必要数据的范围（在以下ID之间没有空格，请使用下一个函数。如果是Excel 365（2019），也可以用作UDF（用户定义函数）。范围可能**未排序**：

功能 提取标签（rngTg As Range）作为变体
  Dim arr，i As Long，dict As Object

 arr = rngTg.Value2 '将范围（不包括标题）放入数组中，以加快处理速度

 设置dict = CreateObject（"Scripting.Dictionary"） '设置提取唯一键的必要字典

 对i = 1 To UBound（arr） '在内存中迭代并用唯一键加载字典和标签项：
    如果不dict.Exists（arr（i，1））然后
        dict.Add arr（i，1），arr（i，3）
    否则
        dict（arr（i，1））= dict（arr（i，1））＆";"& arr（i，3）
    结束如果
 下一个i
  '从字典元素（键和项目）创建2D数组
  ExtractTags = Application.Transpose（Array（dict.keys，dict.Items））
结束功能

它可以用作UDF，在要开始返回的范围中放置公式：

=ExtractTags(A2:C15)

对于无法返回数组的Excel版本，请使用以下子程序：

Sub testExtractTags()
   Dim ws As Worksheet，lastR As Long，arr

   设置ws = ActiveSheet
   lastR = ws.Range（"A"＆ws.rows.count）。End(xlUp).row

   arr = ExtractTags(Range（"A2:C"＆lastR））

   与ws.Range（"E1:F1"）一起
        .Value2 = Array（"TicketID"，"Tag"） '放置标题
        .Offset(1).Resize(UBound(arr)).Value2 = arr '放置处理后的数组内容
        .EntireColumn.AutoFit '自动调整两个涉及的列
    结束与
结束子

这是您提供的VBA代码的翻译部分。如果您需要更多信息，请随时提问。

<details>
<summary>英文:</summary>

And a VBA solution (also tagged), building a range containing only the necessary data (without spaces between the following ID. Please use the next function. If Excel 365 (2019) it can be used as UDF (User Defined Function), too, The range may be **not sorted**:

Function ExtractTags(rngTg As Range) As Variant
Dim arr, i As Long, dict As Object

arr = rngTg.Value2 'place the range (without header) in an array, for faster processing

Set dict = CreateObject("Scripting.Dictionary") 'set the necessary dictionary to extract unique keys

For i = 1 To UBound(arr) 'iterate (in memory) and load dictionary with unique keys and tag Items per key:
If Not dict.Exists(arr(i, 1)) Then
dict.Add arr(i, 1), arr(i, 3)
Else
dict(arr(i, 1)) = dict(arr(i, 1)) & ";" & arr(i, 3)
End If
Next i
'create a 2D array from the dictionary elements (keys and items)
ExtractTags = Application.Transpose(Array(dict.keys, dict.Items))
End Function

It can be used as UDF, placing a formula in the range where to start returning:

=ExtractTags(A2:C15)

For Excel version not being able to return arrays, please use the next sub:

Sub testExtractTags()
Dim ws As Worksheet, lastR As Long, arr

Set ws = ActiveSheet
lastR = ws.Range("A" & ws.rows.count).End(xlUp).row

arr = ExtractTags(Range("A2:C" & lastR))

With ws.Range("E1:F1")
.Value2 = Array("TicketID", "Tag") 'place the header
.Offset(1).Resize(UBound(arr)).Value2 = arr 'drop the processed array content
.EntireColumn.AutoFit 'autofit the two involved columns
End With
End Sub

[![enter image description here][1]][1]


  [1]: https://i.stack.imgur.com/C4kOh.jpg

</details>



# 答案5
**得分**: 2

如果数据按"TicketId"排序，可以使用以下公式。它使用了TEXTJOIN、ROW、INDIRECT和COUNTIF。

在单元格D2中：

```excel
=IF(OR(A1=A2,A1="",A2=""),"",TEXTJOIN(";",TRUE,INDIRECT("C" & ROW() & ":C" & ROW()+COUNTIF(A:A,A2)-1)))

=IF(OR(A1=A2;A1="";A2="");"";TEXTJOIN(";";TRUE;INDIRECT("C" & ROW() & ":C" & ROW()+COUNTIF(A:A;A2)-1)))