# Combine row based on criteria (Product Package)

Occasional 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 Price Subtotal 165932508771 Food SS SSL 1 61600 61600 165932508771 Extras S SL (PACKAGE) 1 0 0 165932508771 Extras O NJ 1 10800 10800 165932508771 Beverage T ETS 1 18700 18700 165932508771 Extras O KP 1 4500 4500 165932508771 Extras O BK 1 2500 2500 165932665478 Food SS SSL 1 61600 61600 165932665478 Extras S SL (PACKAGE) 1 0 0 165932665478 Extras O NJ (PACKAGE) 1 8000 8000 165932665478 Extras O BK 1 2500 2500

What i want to do is:

1. If menu have "PACKAGE" on its name, combine to the cell above that has no "PACKAGE" on its name

2. The output of the combination will be merge of menu name and sum of subtotal, everything else on the row can be deleted

Here is the example of the output i desired:

 Sales Number Menu Category Menu Category Detail Menu Qty Price Subtotal 165932508771 Food SS SSL;SL 1 61600 61600 165932508771 Extras O NJ 1 10800 10800 165932508771 Beverage T ETS 1 18700 18700 165932508771 Extras O KP 1 4500 4500 165932508771 Extras O BK 1 2500 2500 165932665478 Food SS SSL;SL;NJ 1 69600 69600 165932665478 Extras O BK 1 2500 2500

My guess it can be done in power query, unfortunately i'm just beginner in that feature.

14 Replies

# Re: Combine row based on criteria (Product Package)

@Ryan_Izzan Not particularly proud of this solution (attached) as it looks a bit clumsy. But it seems to work on this small scale. See if it works for you in-real-life.

# Re: Combine row based on criteria (Product Package)

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``````

# Re: Combine row based on criteria (Product Package)

Sorry for late response, i just try it today but kinda stuck in "Group" step. it keeps loading and the size reach 1,7 GB and still increasing.

Its my fault that i didn't mention that the table has 23 column and 20.000+ rows. As you mention before, maybe it works on small scale.

Is there any way to solve this problem, or any better solution from my new information

Regards,

# Re: Combine row based on criteria (Product Package)

@Ryan_Izzan Difficult to say what's causing it. 20 thousand rows by 23 columns isn't all that spectacular.

Can you upload or share (Onedrive or similar) a larger and more realistic data set? Not all 20000 rows though but include all columns. Anonymize the data if needed.

# Re: Combine row based on criteria (Product Package)

Please find my dummy version of my data, hope that it can give another input to the solution

# Re: Combine row based on criteria (Product Package)

Here is the smaller one

# Re: Combine row based on criteria (Product Package)

@Ryan_Izzan Will get back to you later and see if I can get it to work on this larger scale.

# Re: Combine row based on criteria (Product Package)

@Riny_van_Eekelen Okay! I noticed some problems. Your initial example was clearly not reflection reality. The logic that worked on the example data does not work on your real data. One of the issues is that you have Sales Numbers where the very first Menu item contains the word PACKAGE.

The first rule was:

"1. If menu have "PACKAGE" on its name, combine to the cell above that has no "PACKAGE" on its name.

Obviously, you don't want to combine SS from sales number ...524 with the items above in ...861 (SSRB,NP and SL).

You could group the entire data set by Sales Number first and then apply the logic that worked on a smaller scale on each of these grouped tables. but you still have to clarify how a situation as in the picture below would have to be handled.

# Re: Combine row based on criteria (Product Package)

I'm so sorry,

When i check, the real data is still same with the condition i  mention.

The data change and the problem you mention just now happen because when i set the dummy data, i just replace word without considering this.

Here is some revised dummy data

Regards,

# Re: Combine row based on criteria (Product Package)

@Ryan_Izzan Okay! No problem. I'll see what I can do.

# Re: Combine row based on criteria (Product Package)

@Ryan_Izzan I added my original query to your file and revised the query a bit. It should works as intended now. But please test it thoroughly.

# Re: Combine row based on criteria (Product Package)

Just finished test it to real data and no problem with the power query, i can load it as table or pivot table.

But when i check the total of "Subtotal" of each month, it different compare to the real data.

It seems when it merge, it duplicate several row that have multiple match.

Any idea how we can solve this?

But anyway i really appreciate and thankful for all your help to me

Regards,

# Re: Combine row based on criteria (Product Package)

@Ryan_Izzan Ah! Omitted to the check. Indeed, you have several Sales numbers the have duplicate items that do not have (PACKAGE) in the Menu column. These lead to being grouped twice. Add one last step to the query that removes duplicates where columns Sales Number, Menu and Subtotal are the same.

So, at the very end, select these tree columns (by holding down Ctrl while selecting). Then right-click and select remove duplicates. The M-code generated looks like this:

Now, the totals should agree.