I have an Excel database and I'm trying avoid doing some manual combining of duplicate data. I've got a bunch of listings that are essentially the same aside from the tags column. What I'd like to have it do is combine these 5 listings into 1 listing, making the categories a comma separated list in a single cell.
我有一个Excel数据库，我尽量避免手工合并重复的数据。除了标签列之外，我还有一堆清单。我想让它做的是将这5个清单合并到1个清单中，使类别在单个单元格中成为一个逗号分隔的列表。
Turn this
把这个
into this
到这个
Is there any way of achieving this? My document has a couple thousand listings, so I'm obviously trying to avoid the manual edit route. I'm an Excel novice, so any hand holding or tutorials you could point me to would be appreciated.
有什么办法可以做到这一点吗?我的文档有几千个列表，所以我显然是想避免手动编辑路径。我是一个Excel新手，所以任何你能给我指点的手或教程都会很感激。
2 个解决方案
#1
22
This can also be done using formulas. For my example to work, the data would need to be sorted by the first column and there would need to be a header row.
这也可以用公式来完成。对于我的示例，数据需要按照第一列进行排序，并且需要有一个头行。
You would need two more columns (C & D). First, add a formula that essentially says to concatenate the data in column B if data in column A is the same as the row above it, otherwise reset the concatenation. The next column would contain a formula to identify the final concatenations so you can sort later.
您还需要另外两列(C和D)。下一列将包含一个公式，用于标识最终的连接，以便稍后进行排序。
This is how I would do it with listings and categories in columns A & B (again, the data would need to be sorted by column A and there would need to be a header row): 
这就是我对A和B列中的列表和类别的处理方式(同样，数据需要按照A列进行排序，并且需要有一个标题行):
Here's the results. Now I would copy the entire range and paste values into another sheet. The rows with zero for column D is what I'd want to use. Sorting by column D would float them to the top. 
这里是结果。现在我将复制整个范围并将值粘贴到另一个表中。D列为0的行是我想要使用的。按D列排序会将它们浮动到顶部。
#2
1
This will (should) generate a new sheet from your source sheet with the duplicates concatenated.
这将(应该)从源表生成一个包含重复连接的新表。
To use the following code you need to add it to a new module in the VBA Editor
要使用以下代码，需要将其添加到VBA编辑器中的新模块
A Shortcut to open the VBA Editor is Alt+F11 (for Windows) and Alt+Fn+F11 (for Mac)
打开VBA编辑器的快捷方式是Alt+F11 (Windows)和Alt+Fn+F11 (Mac)
Once the Editor is open add a new module by selecting it from the "insert" menu in the main menu bar. It should automatically open the module ready to accept code, If not you need to select it (will be named "ModuleN" where N is the next available number) from the project explorer.
一旦编辑器打开，通过在主菜单栏中的“插入”菜单中选择它，添加一个新的模块。如果不需要从project explorer中选择它(将被命名为“ModuleN”，其中N是下一个可用的编号)，那么它应该自动打开准备接受代码的模块。
I'm not sure if the "Scripting.Dictionary" is available in osx, but it cant hurt to try.
我不确定是不是“脚本”。“字典”在osx中是可用的，但是试试也无妨。
Option Explicit
Sub Main()
Dim Source As Worksheet: Set Source = ThisWorkbook.Worksheets("Sheet1")
Dim Destination As Worksheet: Set Destination = ThisWorkbook.Worksheets("Sheet2")
Dim Records As Object: Set Records = CreateObject("Scripting.Dictionary")
Dim Data As Variant
Dim Index As Long
Dim Row As Integer: Row = 1
Data = Source.Range("A1", "B" & Source.Rows(Source.UsedRange.Rows.Count).Row).Value2
For Index = LBound(Data, 1) To UBound(Data, 1)
If Records.Exists(Data(Index, 1)) Then
Destination.Cells(Records(Data(Index, 1)), 2).Value2 = Destination.Cells(Records(Data(Index, 1)), 2).Value2 & ", " & Data(Index, 2)
Else
Records.Add Data(Index, 1), Row
Destination.Cells(Row, 1).Value2 = Data(Index, 1)
Destination.Cells(Row, 2).Value2 = Data(Index, 2)
Row = Row + 1
End If
Next Index
Set Records = Nothing
End Sub
#1
22
This can also be done using formulas. For my example to work, the data would need to be sorted by the first column and there would need to be a header row.
这也可以用公式来完成。对于我的示例，数据需要按照第一列进行排序，并且需要有一个头行。
You would need two more columns (C & D). First, add a formula that essentially says to concatenate the data in column B if data in column A is the same as the row above it, otherwise reset the concatenation. The next column would contain a formula to identify the final concatenations so you can sort later.
您还需要另外两列(C和D)。下一列将包含一个公式，用于标识最终的连接，以便稍后进行排序。
This is how I would do it with listings and categories in columns A & B (again, the data would need to be sorted by column A and there would need to be a header row):
这就是我对A和B列中的列表和类别的处理方式(同样，数据需要按照A列进行排序，并且需要有一个标题行):
Here's the results. Now I would copy the entire range and paste values into another sheet. The rows with zero for column D is what I'd want to use. Sorting by column D would float them to the top.
这里是结果。现在我将复制整个范围并将值粘贴到另一个表中。D列为0的行是我想要使用的。按D列排序会将它们浮动到顶部。
#2
1
This will (should) generate a new sheet from your source sheet with the duplicates concatenated.
这将(应该)从源表生成一个包含重复连接的新表。
To use the following code you need to add it to a new module in the VBA Editor
要使用以下代码，需要将其添加到VBA编辑器中的新模块
A Shortcut to open the VBA Editor is Alt+F11 (for Windows) and Alt+Fn+F11 (for Mac)
打开VBA编辑器的快捷方式是Alt+F11 (Windows)和Alt+Fn+F11 (Mac)
Once the Editor is open add a new module by selecting it from the "insert" menu in the main menu bar. It should automatically open the module ready to accept code, If not you need to select it (will be named "ModuleN" where N is the next available number) from the project explorer.
一旦编辑器打开，通过在主菜单栏中的“插入”菜单中选择它，添加一个新的模块。如果不需要从project explorer中选择它(将被命名为“ModuleN”，其中N是下一个可用的编号)，那么它应该自动打开准备接受代码的模块。
I'm not sure if the "Scripting.Dictionary" is available in osx, but it cant hurt to try.
我不确定是不是“脚本”。“字典”在osx中是可用的，但是试试也无妨。
Option Explicit
Sub Main()
Dim Source As Worksheet: Set Source = ThisWorkbook.Worksheets("Sheet1")
Dim Destination As Worksheet: Set Destination = ThisWorkbook.Worksheets("Sheet2")
Dim Records As Object: Set Records = CreateObject("Scripting.Dictionary")
Dim Data As Variant
Dim Index As Long
Dim Row As Integer: Row = 1
Data = Source.Range("A1", "B" & Source.Rows(Source.UsedRange.Rows.Count).Row).Value2
For Index = LBound(Data, 1) To UBound(Data, 1)
If Records.Exists(Data(Index, 1)) Then
Destination.Cells(Records(Data(Index, 1)), 2).Value2 = Destination.Cells(Records(Data(Index, 1)), 2).Value2 & ", " & Data(Index, 2)
Else
Records.Add Data(Index, 1), Row
Destination.Cells(Row, 1).Value2 = Data(Index, 1)
Destination.Cells(Row, 2).Value2 = Data(Index, 2)
Row = Row + 1
End If
Next Index
Set Records = Nothing
End Sub