CODEWITHSUNDEEP
excelvba
ArnoldasM
ArnoldasM

Reputation: 206

Excel merge similar rows and sum cells

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 enter image description here

Clarification: Here is the output I'm looking for enter image description here

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.

Upvotes: 0

Views: 137

Answers (2)

ArnoldasM
ArnoldasM

Reputation: 206

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

Upvotes: 0

Bitoubi
Bitoubi

Reputation: 116

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

Upvotes: 2

Related Questions