Query4. Ramji - Concatenate Unique assignments as per the Name

Concatenate unique assignments as per the Name!

Today Ramji asked a question: He has a table, in this table Column B has few names and Column A has their respective assignments. Now he wants the output as in Cell C1. See below image:

We will do it by macro, for that we need a helping table.

Step 1. Create a name list of all the Persons. In this example, we will create it in Column E.

Step 2. Open VB Editor by pressing Alt+F11

Step 3. Write this macro.

Or, Copy from here.

Sub AddPLus()

    lr = Range("A1048576").End(xlUp).Row

    r = Range("E1048576").End(xlUp).Row

    For j = 2 To r

    Vlue1 = Range("E" & j).Value

    xx = ""

  

    For i = 2 To lr

        Vlue2 = Range("A" & i).Value

        Vlue3 = Range("B" & i).Value

      

        If Vlue1 = Vlue3 Then

            x = InStr(1, xx, Vlue2)

          

            If x = 0 Then

                xx = xx & "+" & Vlue2

            End If

          

        End If

    Next i

  

    xx = WorksheetFunction.Substitute(xx, "+", "", 1)

    Range("F" & j).Value = xx

    

    Next j

    Set b = Range("E2").CurrentRegion

    For k = 2 To lr

    a = Range("B" & k).Value

        Range("C" & k).Value = WorksheetFunction.VLookup(a, b, 2, 0)

    Next k

    

End Sub

Step 3. Run the macro

Your Output is ready, see below image:

Please note: If you wish you can remove helping table or just put below code before 'End Sub' in macro.

Range("E2").CurrentRegion.Clear

-----------------------------------------------------------------------------------------------------------------------------------------------

Download the file : ConcatenateUniqueAssignments.xlsm

Thanks for learning, please share your feedback in comments if it's helpful.