(Solved) – How to concatenate severals columns using VBA

(solved)-–-how-to-concatenate-severals-columns-using-vba

Let’s say I have this table :

Name

I would want to see :

enter image description here

A follow up from this question:

I can’t for the life of me understand this code in order to add more columns. The code work for ‘Name, Type, Food’ but I need to add ‘Place’ and ‘date’.

Sub Test()

Dim lr As Long, x As Long
Dim arr As Variant
Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")

With Feuil1

    'Get last used row
    lr = .Cells(.Rows.Count, 1).End(xlUp).Row

    'Get array
    arr = .Range("A2:C" & lr).Value

    'Loop through array
    For x = LBound(arr) To UBound(arr)
        If dict.Exists(arr(x, 1) & "|" & arr(x, 2)) Then
            dict(arr(x, 1) & "|" & arr(x, 2)) = Join(Array(dict(arr(x, 1) & "|" & arr(x, 2)), arr(x, 3)), ", ")
        Else
            dict(arr(x, 1) & "|" & arr(x, 2)) = arr(x, 3)
        End If
    Next x

    'Loop through dictionary
    For x = 0 To dict.Count - 1
        .Cells(x   2, 8).Resize(, 2).Value = Split(dict.keys()(x), "|")
        .Cells(x   2, 10).Value = dict.items()(x)
    Next x

End With

End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *