Macro Needed to Concatenate Data in separate ROWS

Originally Posted by jindon

My code are both working on your file.

It may raise the error for the bulky data, so I said post back if you get the error.

Or maybe we are talking about different EXCEL.

Option Explicit

Sub test()
    Dim a, i As Long
    With Range("a1", Range("a" & Rows.Count).End(xlUp)).Resize(, 2)
        a = .Value
        With CreateObject("Scripting.Dictionary")
            .CompareMode = 1
            For i = 1 To UBound(a, 1)
                If a(i, 1) <> "" Then
                    If Not .exists(a(i, 1)) Then
                        .Item(a(i, 1)) = .Count + 1
                        a(.Item(a(i, 1)), 1) = a(i, 1)
                        a(.Item(a(i, 1)), 2) = a(i, 2)
                    Else
                        a(.Item(a(i, 1)), 2) = _
                        Join(Array(a(.Item(a(i, 1)), 2), a(i, 2)), "-")
                    End If
                End If
            Next
            i = .Count
        End With
        .Offset(, .Columns.Count + 2).Resize(i, 2).Value = a
        Application.Goto .Offset(, .Columns.Count + 2).Cells(1), True
    End With
End Sub


Sub testToOtherSheet()
    Dim a, i As Long
    With Sheets("1")
        a = .Range("a1", .Range("a" & Rows.Count).End(xlUp)).Resize(, 2).Value
    End With
    With CreateObject("Scripting.Dictionary")
        .CompareMode = 1
        For i = 1 To UBound(a, 1)
            If a(i, 1) <> "" Then
                If Not .exists(a(i, 1)) Then
                    .Item(a(i, 1)) = .Count + 1
                    a(.Item(a(i, 1)), 1) = a(i, 1)
                    a(.Item(a(i, 1)), 2) = a(i, 2)
                Else
                    a(.Item(a(i, 1)), 2) = _
                    Join(Array(a(.Item(a(i, 1)), 2), a(i, 2)), "-")
                End If
            End If
        Next
        i = .Count
    End With
    With Sheets("2").Cells(1).Resize(i, 2)
        .CurrentRegion.ClearContents
        .Value = a
    End With
End Sub

Hello,

Your code works on the test file;
But If I try the macro on the original file (larger) It doesn't appears nothing on second sheet.






thanks



thanks