vba Excel VBA将多行数据连接成1行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13306919/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA to concatenate data from multiple rows into 1 row
提问by user1811852
Possible Duplicate:
Excel Concatenate Rows
可能的重复：
Excel 连接行
I want to merge several rows of data into a single row with an Excel VBA macro, and for the output to be on a new worksheet, but I don't know where to start. The data is fairly simple, and consists of a column of IDs and a column of products. There may be multiple lines with the same ID if multiple products entries exist, so I'm looking for a macro to loop through the dataset and merge these multiple entries into one. If the row is unique (i.e. there is only one product entry against that ID) then the row can just be copied to the output sheet without any merging.
我想使用 Excel VBA 宏将多行数据合并为一行，并将输出放在新工作表上，但我不知道从哪里开始。数据相当简单，由一列 ID 和一列产品组成。如果存在多个产品条目，则可能有多行具有相同的 ID，因此我正在寻找一个宏来遍历数据集并将这些多个条目合并为一个。如果该行是唯一的（即该 ID 只有一个产品条目），则该行可以直接复制到输出表中，无需任何合并。
The data looks like this:
数据如下所示：
ID PRODUCT
1-10OOMD6 Product SKU1 (9000)
1-10PQFV3 Product SKU2 (7000)
1-10QSCSB Product SKU3 (50)
1-10RWX7L Product SKU4 (2)
1-10TKZQJ Product SKU5 (1)
1-10UROIL Product SKU3 (12000)
1-10UROIL Product SKU7 (12000)
1-10UROIL Product SKU8 (12000)
1-10UW6KU Product SKU9 (1000)
1-10UW6KU Product SKU10 (6500)
1-10W5HR9 Product SKU11 (80)
1-10XZWX7 Product SKU12 (1)
1-10ZRXVJ Product SKU5 (70)
1-10ZV49V Product SKU3 (250)
1-110RQQN Product SKU13 (1)
1-110RQQN Product SKU2 (600)
1-110RQQN Product SKU14 (1)
1-110RQQN Product SKU4 (600)
1-112DJ2N Product SKU15 (5)
1-112EWRQ Product SKU16 (3000)
And I want it to end up like this:
我希望它以这样的方式结束：
ID PRODUCT
1-10OOMD6 Product SKU1 (9000)
1-10PQFV3 Product SKU2 (7000)
1-10QSCSB Product SKU3 (50)
1-10RWX7L Product SKU4 (2)
1-10TKZQJ Product SKU5 (1)
1-10UROIL Product SKU3 (12000), Product SKU7 (12000), Product SKU8 (12000)
1-10UW6KU Product SKU9 (1000),Product SKU10 (6500)
1-10W5HR9 Product SKU11 (80)
1-10XZWX7 Product SKU12 (1)
1-10ZRXVJ Product SKU5 (70)
1-10ZV49V Product SKU3 (250)
1-110RQQN Product SKU13 (1), Product SKU2 (600), Product SKU14 (1), Product SKU4 (600)
1-112DJ2N Product SKU15 (5)
1-112EWRQ Product SKU16 (3000)
So that each ID on the output sheet is unique.
这样输出表上的每个 ID 都是唯一的。
A few points:
几点：
- I couldn't paste images, so to clarify: this is an ID: 1-10OOMD6, and this is a product: Product SKU1 (9000)
- The dataset is large, at least 35,000 rows
- The data isn't initially sorted by ID, but easily can be.
- It would be great to separate the products with a comma.
- I would like to output of the macro on another worksheet.
- 我无法粘贴图片，所以澄清一下：这是一个 ID：1-10OOMD6，这是一个产品：产品 SKU1 (9000)
- 数据集很大，至少有 35,000 行
- 数据最初不是按 ID 排序的，但很容易可以。
- 用逗号分隔产品会很棒。
- 我想在另一个工作表上输出宏。
Any help would be greatly appreciated! This would save me an enormous amount of time! Thank you.
任何帮助将不胜感激！这将为我节省大量时间！谢谢你。
回答by Christian Sauer
As a starting point: Use a dictionary - how this can be done, look at this article: http://www.techbookreport.com/tutorials/vba_dictionary.html
作为起点：使用字典 - 如何做到这一点，请看这篇文章：http: //www.techbookreport.com/tutorials/vba_dictionary.html
After that, just do something like that (Pseudocode, will not work as vba initially you have work to do):
在那之后，就做这样的事情（伪代码，最初你有工作要做，不会像 vba 一样工作）：
Dim dict as new dictionary.
for i=1 to activesheet.rows.count
Dim Key= activesheet.cells(i,1).value 'get the unique product-id
Dim Product = activesheet.cells(i,2).value 'get the product-sku
If dic.exists(Key)=false then 'the heart of this program: if key doenst exist, add a new one, if it alreadys exits, append data
dict.add(Key, Product)
else
dict.Item(Key) = dict.Item(Key) & " " & Product
end if
next
For Each v In dict.Keys
'write it to your sheet
Next
I would guess that this approach is faster than the other one, simply because dictionaries are fast and it does not involve multiple find-loops.
我猜想这种方法比另一种方法快，因为字典速度很快，而且它不涉及多个查找循环。
回答by Alan K
iDevelop's comment is quite correct; the site encourages more "try and then post only when you get stuck" type questions. However I do know that it can be hard when you don't even know where to start!
iDevelop 的评论非常正确；该网站鼓励更多“尝试，然后在遇到困难时才发布”类型的问题。但是我知道，当您甚至不知道从哪里开始时，这可能会很困难！
Here's where I would.
这就是我想去的地方。
First I'd put an extra column of formulas to the right of your products. The formula that I would put in cell C2 would be:
首先，我会在您的产品右侧添加一列额外的公式。我要放在单元格 C2 中的公式是：
=COUNTIF($A:A1,A2)
which would then be copied down becoming
然后将其复制下来成为
=COUNTIF($A:A2,A3)
=COUNTIF($A:A3,A4)
and so on. What's the purpose of this? It counts the number of ID codes which are the same as the row that you're on but which appear on an EARLIER row. If that number is >0 then you know that there was a previous occurrence of the ID code.
等等。这样做的目的是什么？它计算与您所在的行相同但出现在较早的行上的 ID 代码的数量。如果该数字大于 0，则您知道该 ID 代码之前出现过。
Next, I would build some code to iterate through the UsedRange of your sheet. Look at column C of each row. If it is 0, then simply copy the ID and description to the new sheet. If it's NOT, then use the Cells.Find method to locate the earlier entry in your target sheet, and append the description to that cell.
接下来，我将构建一些代码来遍历工作表的 UsedRange。查看每行的 C 列。如果它是 0，那么只需将 ID 和描述复制到新工作表中。如果不是，则使用 Cells.Find 方法在目标工作表中找到较早的条目，并将说明附加到该单元格。
There are probably other ways of doing it but the code should be easy enough to write and run acceptably fast.
可能还有其他方法可以做到这一点，但代码应该足够简单，可以快速编写和运行。
Give it a go and see what you come up with, and by all means post back if you get stuck. (Though I won't be around for a few hours since it's bed time here; plenty of other people around the world doubtless will, though.)
试一试，看看你想出了什么，如果你遇到困难，一定要回帖。（虽然我不会在这里待几个小时，因为现在是睡觉时间；不过，世界各地的许多其他人无疑会。）
Good luck!
祝你好运！
