The following VBA code also can help you consolidate duplicate rows in the worksheet, but at the same time, the original data will be destroyed, you need to backup a copy of the data. Hold down the ALT F11 keys, then it opens the Microsoft Visual Basic for Applications window. Click Insert Sub Combine Rows() 'Update 20130829 Dim Work Rng As Range Dim Dic As Variant Dim arr As Variant On Error Resume Next x Title Id = "Kutoolsfor Excel" Set Work Rng = Application. And then click another column name that you want to combine the data, and click Combine to choose a separator to separate your combined values, see screenshot: 4.
Like before, click on ‘Consolidate’ in the ‘Data Tools’ selection of the ‘Data’ tab. Now you need to select the range that includes the column headers and row labels.
Select the data range that you want to use, and then click Kutools Advanced Combine Rows to enable the Advanced Combine Rows dialog box. In the Advanced Combine Rows dialog box, click the column name that you want to combine other data based on, and click Primary Key, see screenshot: 3.
See screenshots: Sometimes, you want to combine the rows based on duplicate values in another column, the Advanced Combine Rows of Kutools for Excel also can do a favor for you, please do as follows: 1.
Note: If you want to use this code, you’d better make a copy of the file to avoid destroying the data, and this code is only applied to two columns. And then select the column name you want to sum the values and click Calculate Sum or other calculations as you need. Click Ok to close dialog, then you can see the duplicates are combined and the corresponding data in another column are added up together.
Then press F5 key to run this code, and you need to select the range that you want to consolidate in the popped out prompt box. And then click OK, the duplicate rows have been combined and the values are added up. In the Advanced Combine Rows dialog, check My data has headers if your range have headers, and select column name which you want to combine the duplicates and click Primary Key, see screenshot: 3.