Click on another column name that you want to combine the data, then click combine to select a separator to differentiate your combined values Click the column name you intend to combine other data from the Advanced combine Rows dialog box. Go to advanced combine Rows to enable the Advanced combine Rows dialog boxĤ. Click 'Kutools' then 'merge and split.'ģ. Choose the data range that you want to useĢ. To combine Rows based on duplicate values in another column, use these steps:ġ. Combining corresponding rows based on duplicate values in another column using Kutools for Excel The duplicates will be combined, and the corresponding data in another column will be summed up. Choose the column name you want to sum the values, then click calculate, sum, or any other calculations you need. If the range has headers, then select the column name you want to combine the duplicates and click a primary keyĤ. Check 'My data has headers' from the Advanced combine Rows dialog
Choose the range you want and click Kutools then 'merge and split' then Advanced combine Rowsģ.
MERGE DUPLICATE ROWS IN EXCEL 2013 CODE
The code is only applied to two columns Using Kutools for ExcelĢ. To use the code, you will have to make a copy of the file to prevent data destruction. However, the original data will be destroyed hence the need to back up the copy of the data. The VBA code helps to consolidate duplicate rows in the worksheet. The duplicate rows will be combined and added up The VBA code after selecting the range that you want to consolidate from the prompt box Sub CombineRows() Dim lr As Long, r As Long lr = Range("A" & Rows.Count).End(xlUp).Row Application.ScreenUpdating = False Range("A2:E" & lr).Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlNo For r = lr To 3 Step -1 If Cells(r, 1).Value = Cells(r - 1, 1).Value Then Range("B" & r, Range("A" & r).End(xlToRight)).Copy Destination:=Cells(r - 1, Columns.Count).End(xlToLeft).Offset(, 1) Rows(r).Delete End If Next r For r = 6 To Range("A1"). Step 4 Cells(1, r).Resize(, 4).Value = Range("B1:E1").Value Next r Application.ScreenUpdating = True End Sub The VBA code: combine duplicate rows and sum the values Click insert then Module and paste the VBA code in the module window. The "Microsoft visual basic for applications window" will openĢ. The duplicates will be combined and summed. After completing the settings, select Ok. Look at the top row and left column from the use labels in the option.Ĥ.Click on the icon with an arrow to choose the range you want to consolidate, then tap the "Add button" to add the reference to all references in The All References list.Select the sum from the function drop-down list.Click the cell where you want to locate the results in your current worksheetĢ. To use the Consolidate function, follow these steps :ġ.
It is used to summarize multiple rows based on the duplicates. The Consolidate Function is useful in consolidating multiple worksheets or rows in Excel. Combining corresponding rows based on duplicate values in another column using Kutools for Excel.
0 kommentar(er)
