vba 连接具有匹配 ID 的行

Concatenate Rows with Matching IDs

提问by Kode

I have multiple rows of purchase details. Each purchase has a client ID. For presentation purposes I need to merge purchases with a similar client ID into a single cell so I can use a VLOOKUP to display this in another table that has client information. Any ideas?

我有多行购买详细信息。每次购买都有一个客户 ID。出于演示目的，我需要将具有类似客户 ID 的购买合并到一个单元格中，以便我可以使用 VLOOKUP 将其显示在另一个包含客户信息的表中。有任何想法吗？

In the example below, I'd like cell C2 to contain "1, 2", cell C3 to contain "3" and cell C4 to be empty (bill has made no purchases).

在下面的示例中，我希望单元格 C2 包含“1, 2”，单元格 C3 包含“3”，单元格 C4 为空（比尔没有购买）。

     A       B      C
1 client_id name  purchase_ids
2         1 jim
3         2 bob
4         3 bill


purchase_id purchase_client_id amount
          1                  1    100
          2                  1    500
          3                  2     50

回答by Seketman

You can create a Dynamic Pivot Table into new sheet, to summarize sales by ClientID, and then use that table with VLOOKUP (http://www.tips-for-excel.com/2011/06/how-to-make-a-pivot-table/).

您可以在新工作表中创建动态数据透视表，按 ClientID 汇总销售额，然后将该表与 VLOOKUP ( http://www.tips-for-excel.com/2011/06/how-to-make-a -数据透视表/）。

Example data sheet

示例数据表

Pivot table summarized by ClientID

由 ClientID 汇总的数据透视表

回答by Han Soalone

Here another suggestion, do a function that gathers the data in one cell with VBA. Done this some time ago, but you can use & edit it for your own purpose -

这是另一个建议，使用 VBA 执行一个将数据收集在一个单元格中的函数。前段时间完成此操作，但您可以出于自己的目的使用和编辑它 -

Option Explicit
Public Function STRINGCONCATENATEVLOOKUP(ByVal r As Range, ByVal criteria As Variant, Optional ByVal colnum As Long, Optional ByVal separator As String) As String
On Error GoTo err_hand

Dim n As Long
Dim result As String

If colnum = Empty Then colnum = r.Columns.Count

If colnum > r.Columns.Count Or colnum < 1 Then
    STRINGCONCATENATEVLOOKUP = "#COLVALUE!"
    Exit Function
End If

If separator = "" Then separator = ";"

For n = 1 To r.Rows.Count Step 1
    If r.Cells(n, 1).Value = criteria Then result = result & r.Cells(n, colnum).Value & separator
Next
result = Left(result, Len(result) - Len(separator))

STRINGCONCATENATEVLOOKUP = result
Exit Function

err_hand:
    STRINGCONCATENATEVLOOKUP = CVErr(xlErrValue)
End Function

Function works just like VLOOKUP, but with the difference it sums all data and returns a string separated by ";" or what you define.

函数的工作方式与 VLOOKUP 类似，但不同之处在于它对所有数据求和并返回一个以“;”分隔的字符串。或者你定义的。

回答by LondonRob

I'm afraid you're going to have to get your hands dirty with VBA (macro programming) to do what you want to do.

恐怕您将不得不使用 VBA（宏编程）来做您想做的事情。

There is no Excel function which can create a concenated list. The Excel function CONCATENATEdoesn't do what you need:

没有可以创建合并列表的 Excel 函数。Excel 函数CONCATENATE无法满足您的需求：

=CONCATENATE(A1, "-", B1) # returns "foo-bar" if A1 has 'foo' and B1 has 'bar'

So VBA is what you'll need. Fortunately, othershave been here before, including this SO answer.

所以VBA就是你所需要的。幸运的是，其他人以前也来过这里，包括这个SO answer。

回答by user3616725

my answer requires MOREFUNC addon*

我的回答需要 MOREFUNC 插件*

Here I assume data in purchase "table" is in A9:C11. Adjust accordingly.

在这里，我假设购买“表”中的数据在 A9:C11 中。相应调整。

formula for C2: {=MCONCAT(IF($B$9:$B$11=A2,$A$9:$A$11,""),",")}

C2的公式： {=MCONCAT(IF($B$9:$B$11=A2,$A$9:$A$11,""),",")}

notice the curly braces. This is an array formula you have to confirm using Ctrl+Shift+Enter, not just Enter

注意花括号。这是一个数组公式，您必须使用 确认Ctrl+Shift+Enter，而不仅仅是Enter

then copy the formula to C3 and C4

然后将公式复制到 C3 和 C4

---

---

MOREFUNC ADDON

更多功能插件

• Morefunc Addon is a free library of 66 new worksheet functions.
• HEREis some information (by original author)
• here is the last working download linkI found
• here is a good installation walk-through video

• Morefunc Addon 是一个包含 66 个新工作表函数的免费库。
• 这是一些信息（由原作者提供）
• 这是我找到的最后一个可用的下载链接
• 这是一个很好的安装演练视频