Merging Multiple Cell Values into Excel

1) In the Excel list, do the following:

  • Sort the list in the order

  • Use the below suggested code to write a User Defined Function (UDF) in VBA to rerrange information in the Excel list as the format below.

Mary Form1
Mary Form2
Ken Form1
Bob Form3
Bob Form1

 

 TURN IT INTO

 

Mary Form1, Form2
Bob Form3, Form1
Ken Form1

  • Make sure enable the Developer tab (File tab > Options > Customize Ribbon > select Developer check box under Main Tabs hierarical menu) and select Visual Basic command

  • In the VBA window, paste the below code in the standard Module B (click the down arrow next to InsertUser Form icon on the Toolbar > Module)

Sub Rearrange()
    Dim lr As Long, r As Long
    
    Application.ScreenUpdating = False
    lr = Range("A" & Rows.Count).End(xlUp).Row
    For r = lr To 2 Step -1
        With Cells(r, 1)
            If .Value = .Offset(-1).Value Then
                .Offset(-1, 1).Value = .Offset(-1, 1).Value & ", " _
                                        & .Offset(, 1).Value
                .EntireRow.Delete
            End If
        End With
    Next r
    Columns("B").AutoFit
    Application.ScreenUpdating = True
End Sub

  • Leave the VBA window

  • Select the entire of Excel list  

  • Under Develop tab, click Marco command

  • In the Macro dialog box, select the macro name called Rearrange > click Run button

  • Save the Excel list either with Macro-embed or as a regular Excel file

2) In the Excel database file, merge the data between two spreadsheets by combining two formulas INDEX and MATCH

  • INDEX function: =INDEX(data_range, row_number, column_number)

‌


‌

  • INDEX and MATCH combined function:

‌