EXCEL：合并多行宏 [英] EXCEL: Merge Multiple Rows Macro

问题描述

我需要一个MACRO来查看COL A的所有实例，并将COL B的所有值组合成一行，同时删除该进程中的重复项。添加逗号是一个加号。

I need a MACRO that looks at all instances of COL A and combines all values of COL B into one row, while deleting the duplicates in the process. Adding a comma is a plus.

我不知道任何VBA，但如果有人够善于解释，我很乐意学习。这不是我需要的第一个VBA解决方案。谢谢！

I don't know any VBA, but if someone is kind enough to explain, I would love to learn. This isn't the first VBA solution I've needed. Thanks!

我需要的例子：

COL A    COL B 
100 ---- PC 245
100 ---- PC 246
100 ---- PC 247
101 ---- PC 245
101 ---- PC 246
101 ---- PC 247

INTO

COL A    COL B 
100 ---- PC 245, PC 246, PC 247
101 ---- PC 245, PC 246, PC 247

此数据正在进行一个地图，所以我需要它连接工具提示文本。任何帮助是赞赏。谢谢！

This data is going into a map, so I need it concatenated for the tooltip text. Any help is appreciated. Thanks!

PS：我需要的是一个MACRO。我不需要的是一个PIVOT TABLE。

PS: What I need is a MACRO. What I don't need is a PIVOT TABLE.

推荐答案

由主持人删除。 @ bill-the-lizard，在删除之前，你可以评论我的答案有什么问题吗？

Reposting this code as it was deleted by a moderator. @bill-the-lizard, prior to redeleting it, can you comment on what's wrong with my answer?

Sub ConsolidateRows()
'takes rows and consolidate one or many cells, based on one or many cells matching with above or below rows.

Dim lastRow As Long, i As Long, j As Long
Dim colMatch As Variant, colConcat As Variant

'**********PARAMETERS TO UPDATE****************
Const strMatch As String = "A"    'columns that need to match for consolidation, separated by commas
Const strConcat As String = "B"     'columns that need consolidating, separated by commas
Const strSep As String = ", "     'string that will separate the consolidated values
'*************END PARAMETERS*******************

application.ScreenUpdating = False 'disable ScreenUpdating to avoid screen flashes

colMatch = Split(strMatch, ",")
colConcat = Split(strConcat, ",")

lastRow = range("A" & Rows.Count).End(xlUp).Row 'get last row

For i = lastRow To 2 Step -1 'loop from last Row to one

    For j = 0 To UBound(colMatch)
        If Cells(i, colMatch(j)) <> Cells(i - 1, colMatch(j)) Then GoTo nxti
    Next

    For j = 0 To UBound(colConcat)
        Cells(i - 1, colConcat(j)) = Cells(i - 1, colConcat(j)) & strSep & Cells(i, colConcat(j))
    Next

    Rows(i).Delete

nxti:
Next

application.ScreenUpdating = True 'reenable ScreenUpdating
End Sub

这篇关于EXCEL：合并多行宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！