Merge cells in Column B based on sells in column A

[timspin]

Hi All

I have looked through the archive and cant find a solution to my problem - hope someone can help.

I have two columns of data (a few thousand rows), I want to merge all the table names into 1 cell for each variable name. Each variable has a varying number of tables

Row...Column A.....................Column B
1.......Variable Name..............Table Name
2.......account_number...........auto_commercial_vehicle_t
3.........................................auto_covered_symbol_t
4.........................................auto_transaction_details_t
5.........................................coverage_summary_t
6.........................................nb_summary_t
7.........................................policy_summary_t
8.........................................transaction_details_t
9........acct_num...................opp_smry_t
10.......................................pol_t
11......annl_sale_amt.............opp_smry_t
12.......................................pol_t
13......annual_sale_amount....nb_summary_t
14.......................................policy_summary_t
15......app_sys_cde...............pol_t

So for the table above I would see cell a2 'account_number' and cell b2 a list of the associated table names (in a list separated by new lines, or carriage returns)
My ideal results would look like this, with 1 row per variable name.

Row....Column A.................Column B

1........Variable Name.........Table Name
2........account_number......auto_commercial_vehicle_t
.......................................auto_covered_symbol_t
.......................................auto_transaction_details_t
.......................................coverage_summary_t
.......................................nb_summary_t
.......................................policy_summary_t
.......................................transaction_details_t
3........acct_num................opp_smry_t
.......................................pol_t
4........annl_sale_amt.........opp_smry_t
.......................................pol_t

Note: My data comes from a pivot table so I could start with each variable name for each table like this, if that helps the end solution.

Row....Column A....................Column B

1........Variable Name.............Table Name
2........account_number..........auto_commercial_vehicle_t
3........account_number..........auto_covered_symbol_t
4........account_number..........auto_transaction_details_t
5........account_number..........coverage_summary_t
6........account_number..........nb_summary_t
7........account_number..........policy_summary_t
8........account_number..........transaction_details_t

Thanks all.
Tim

 

[DanteAmor]

Try this.
the result will be in columns C and D

Sub Merge_Cells()


    Dim i As Double, j As Double, u As Double
    j = 1
    Range("D2:E" & Rows.Count).Clear
    For i = 2 To Range("B" & Rows.Count).End(xlUp).Row
        If Cells(i, "A").Value = "" Then
            Cells(j, "E").Value = Cells(j, "E").Value & Chr(10) & Cells(i, "B").Value
        Else
            j = j + 1
            Cells(j, "D").Value = Cells(i, "A").Value
            Cells(j, "E").Value = Cells(i, "B").Value
         End If
    Next
    
    u = Range("D" & Rows.Count).End(xlUp).Row
    Range("D2:E" & u).VerticalAlignment = xlTop
    Rows("2:" & u).EntireRow.AutoFit


End Sub

 

[timspin]

Hi Dante

I copied and pasted this into the VB editor and ran it (straight from editor and from Macro menu) - got error 'Run time error 16 Expression to complex' when I debug it highlights this row - For i = 2 To Range("B" & Rows.Count).End(xlUp).Row

Thanks
Tim

 

[DanteAmor]

Use this:

Sub Merge_Cells()


    Dim i As Double, j As Double, u As Double
    
    Application.ScreenUpdating = False
    
    j = 1
    Range("D2:E" & Rows.Count).Clear
    u = Range("B" & Rows.Count).End(xlUp).Row
    For i = 2 To u
        If Cells(i, "A").Value = "" Then
            Cells(j, "E").Value = Cells(j, "E").Value & Chr(10) & Cells(i, "B").Value
        Else
            j = j + 1
            Cells(j, "D").Value = Cells(i, "A").Value
            Cells(j, "E").Value = Cells(i, "B").Value
         End If
    Next
    
    u = Range("D" & Rows.Count).End(xlUp).Row
    Range("D2:E" & u).VerticalAlignment = xlTop
    Rows("2:" & u).EntireRow.AutoFit


    Application.ScreenUpdating = True
    
    MsgBox "End"
End Sub