CODEWITHSUNDEEP
excelexcel-formulaconcatenation
Goran Zooferic
Goran Zooferic

Reputation: 473

How can i concatenate row text with comma in excel?

I have an excel data as follow and i want to concatenate skills that seperated by comma according by person name like this; 

Irakli Beridze     |  C#, Python, Java
Parpali Zurashvili |  C++, C

I can achieve according to followed sceanorio but i have n pieces data row.

enter image description here

Upvotes: 0

Views: 9136

Answers (2)

user2140261
user2140261

Reputation: 7993

If you don't mind VBA you can use the following: 

Sub ConcatRows()
Dim arr As Variant
Dim i As Long
Dim d As Dictionary

'Create a dictionary to hold all Name and Skill Values
Set d = CreateObject("Scripting.Dictionary")

'Fill an array with all Values
arr = Range("A2", Cells(Rows.Count, 2).End(xlUp))

'Loop the Values and and them into a dictionary
For i = LBound(arr) To UBound(arr)

    'If Name already in list then Add Skill to Item value of Name Key
    If d.Exists(arr(i, 1)) Then
        d(arr(i, 1)) = d(arr(i, 1)) & ", " & arr(i, 2)
    'If Name isn't already in list then add name with its first Skill
    Else
        d.Add arr(i, 1), arr(i, 2)
    End If

Next i

'Write all Name back to Worksheet
Range("A2").Resize(d.Count) = Application.Transpose(d.Keys)
'Write all Skills Back to worksheet
Range("B2").Resize(d.Count) = Application.Transpose(d.Items)
End Sub

Upvotes: 1

Jerry
Jerry

Reputation: 71538

You can do it using a helper column like this:

  1. Insert filters and sort by name:

    enter image description here

  2. In cell C2, put the formula:

    =IF(A2=A3,0,1)

    enter image description here

    0 will be where the 'copies' will be and 1 will be where the final line to be kept will be.

  3. In cell D2, put the value of B2, and in D3, put the following formula:

    =IF(A3=A2,D2&", "&B3,B3)

    enter image description here

  4. Now that that's done, copy and paste values in column D (Copy whole column, use paste special and pick 'Values'). Remove the filter, add back the filter but this time on all 4 columns and filter on 0 in column C:

    enter image description here

  5. Delete those rows and clear the filters. Finally, sort column A:

    enter image description here

You can now delete columns B and C.

Upvotes: 5

Related Questions