Excel merge similar rows and sum cells

Question

what would be the best way to merge similar rows (only order number letter different a4;a6;a8 and produced quantity) and sum (produced quantity e4;e6;e8) cells? This is how excel table looks

Clarification: Here is the output I'm looking for

Rows 4;6;8 are the same except Order column (one letter added on 6 and 8) and Produced Column (different produced quantity). Rows 4,6,8 are merged and produced quantity is summed. Rows 6,8 is hidden or deleted.

Can you clarify what you mean by "merge similar rows"? Perhaps a sample of what you expect the output to look like would help. What have you tried so far? — BruceWayne, Oct 27 '16 at 15:38

score 2 · Accepted Answer · answered Oct 27 '16 at 15:44

2

Here is an example that could solve your problem:

Sub test()

i = 1
produced = 0
While Cells(i, 1) <> "" Or Cells(i + 1, 1) <> ""
    If Cells(i, 1) <> "" Then

        produced = Cells(i, 5)

        j = 1
        'second loop to add up every line with the same order, then suppress the lines
        While Cells(j, 1) <> "" Or Cells(j + 1, 1) <> ""
            If Left(Cells(j, 1), 7) = Left(Cells(i, 1), 7) And i <> j Then
                produced = produced + Cells(j, 5)
                Cells(j, 5).EntireRow.Select
                Selection.Delete Shift:=xlUp
                j = j - 1
            End If

            j = j + 1
        Wend

    End If

i = i + 1
Wend

answered Oct 27 '16 at 15:44

Bitoubi

116
8

Thank you @Bitoubi for help. In full version of my excel sheet produced column is 20, so I have changed Cells(j, 5) to Cells(j, 20). and my order number is 123456-7 or 123456-78 (eight or nine symbols lenght) so I have changed Cells(j, 1), 7 to Cells(j, 1), 9 but still can't get it working. Maybe you know what wrong could it be? and btw, workbook has from 200 to 500 rows. – ArnoldasM Oct 27 '16 at 16:25
Thank you, with a little bit of modification, I've managed to make it working. :) – ArnoldasM Oct 27 '16 at 16:43
1

Nice - I highly suggest though that you [remove the use of `.Select`](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). The line would just be `Cells(j,5).EntireRow.Delete Shift:=xlUp` – BruceWayne Oct 27 '16 at 17:16
@BruceWayne Yes, I have removed select. Posted my full solution, which helped me. – ArnoldasM Oct 27 '16 at 17:34

score 0 · Answer 2 · answered Oct 27 '16 at 17:33

Ok, here is the modified @Bitoubi code which helped me:

Sub RemoveSplitOrders()
i = 1
produced = 0
While Cells(i, 1) <> "" Or Cells(i + 1, 1) <> ""
    If Cells(i, 1) <> "" Then

        produced = Cells(i, 20)

        j = 1
        'second loop to add up every line with the same order, then suppress the lines
        While Cells(j, 1) <> "" Or Cells(j + 1, 1) <> ""
            If Left(Cells(j, 1), 8) = Left(Cells(i, 1), 8) Or Left(Cells(j, 1), 9) = Left(Cells(i, 1), 9) Then
                If Cells(j, 2) = Cells(i, 2) And i <> j Then
                    produced = produced + Cells(j, 20)
                    Cells(i, 20).Value = produced
                    Range(Cells(j, 20), Cells(j + 1, 20)).EntireRow.Delete Shift:=xlUp
                    j = j - 1
                End If
            End If

            j = j + 1
        Wend

    End If

i = i + 1
Wend
End Sub

Excel merge similar rows and sum cells

2 Answers2