时间戳

首页 > 解决方案 > Excel：根据另一列的重复项复制列中的单元格

excel - Excel：根据另一列的重复项复制列中的单元格

问题描述

我想根据 B 列中的重复项从 A 列复制数据，然后在 C 列中将它们以逗号分隔或水平列出。请看下面的例子：

Column A   Column B   Column C (Output)
1          A          1,2
2          A          
3          B          3,4,5
4          B
5          B
6          C          6
7          D          7

标签： excelexcel-formula

解决方案

Sub CopyCells()
Application.ScreenUpdating = False
Dim i As Long, v As Variant, fVisRow As Long, rng As Range, Val As String
v = Range("B2", Range("B" & Rows.Count).End(xlUp)).Value
With CreateObject("Scripting.Dictionary")
    For i = 1 To UBound(v, 1)
        If Not .Exists(v(i, 1)) Then
            .Add v(i, 1), Nothing
            With Range("A1").CurrentRegion
                .AutoFilter 2, v(i, 1)
                fVisRow = .Range("A2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible).Cells(1, 1).Row
                For Each rng In Range("A2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible)
                    If Val = "" Then Val = rng Else Val = Val & ", " & rng
                Next rng
                Range("C" & fVisRow) = Val
                Val = ""
            End With
        End If
    Next i
End With
Range("A1").AutoFilter
Application.ScreenUpdating = True
End Sub

推荐阅读