Forum Discussion
Ryan_Izzan
Sep 14, 2022Copper Contributor
Combine row based on criteria (Product Package)
Hi everyone, I am currently doing data analysis for my restaurant and got stuck in a process. I have set of data below: Sales Number Menu Category Menu Category Detail Menu Qty Pr...
HansVogelaar
Sep 14, 2022MVP
As an alternative, here is a macro solution.
Sub MergePackage()
Dim r As Long
Dim n As String
Dim s As Double
Dim p As Long
Application.ScreenUpdating = False
r = Range("D" & Rows.Count).End(xlUp).Row
Do
p = InStr(Range("D" & r), "(PACKAGE)")
If p Then
Do
n = n & ";" & Left(Range("D" & r).Value, p - 1)
s = s + Range("G" & r).Value
Range("A" & r).Resize(1, 7).Delete Shift:=xlShiftUp
r = r - 1
If r = 1 Then Exit Do
p = InStr(Range("D" & r), "(PACKAGE)")
Loop Until p = 0
Range("D" & r).Value = Range("D" & r).Value & n
Range("G" & r).Value = Range("G" & r).Value + s
n = ""
s = 0
End If
r = r - 1
Loop Until r = 1
Application.ScreenUpdating = True
End Sub