excel行以逗号分隔 [英] excel row to comma separated

问题描述

  ID值
 1 value1 
 1 value2 
 1 value3 
 2 value1 
 2 value2 
 3 value1 
 3 value2 
  

等

我想将其转换为（创建新的表格）

  ID值
 1 value1，value2，value3 
 2 value1，value2 
 3 value1，value2，value3 
  

基本上，我希望具有相同ID的所有行都加入逗号分隔的所有值。

谢谢，

解决方案

请尝试以下代码：

  Sub sample（）
 Dim i As Integer，j As Integer 
 Dim temp As String 
 Range（A：A ）.AdvancedFilter动作：= xlFilterCopy，copytorange：= Range（C1），unique：= True 
 
 Dim lastRow As Long 
 lastRow = Range（C65000 xlUp）.Row 
 
j = 1 
对于i = 2 To lastRow 
直到细胞（j，2）=
如果Trim（Cells（j，1））= Trim（Cells（i，3））然后
 temp = temp& ，&单元格（j，2）
 End If 
j = j + 1 
循环
单元格（i，4）= temp 
 temp =
j = 1 
下一个
 End Sub 
  

I have an xls file with values of the following:

ID Value
1 value1
1 value2
1 value3
2 value1
2 value2
3 value1
3 value2

etc

I would like to transform this to (create a new sheet)

ID Values
1 value1,value2,value3
2 value1,value2
3 value1,value2,value3

Basically, I would like all rows that have the same ID to join all values comma separated.

Thanks,

解决方案

Try below code :

Sub sample()
    Dim i As Integer, j As Integer
    Dim temp As String
    Range("A:A").AdvancedFilter Action:=xlFilterCopy, copytorange:=Range("C1"), unique:=True

    Dim lastRow As Long
    lastRow = Range("C65000").End(xlUp).Row

    j = 1
     For i = 2 To lastRow
        Do Until Cells(j, 2) = ""
            If Trim(Cells(j, 1)) = Trim(Cells(i, 3)) Then
                temp = temp & "," & Cells(j, 2)
            End If
            j = j + 1
        Loop
        Cells(i, 4) = temp
        temp = ""
        j = 1
    Next
End Sub

这篇关于excel行以逗号分隔的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！