Sep 14 2022 02:19 AM
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.
Please Help.
Sep 14 2022 03:23 AM
@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.
Sep 14 2022 03:27 AM
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
Sep 19 2022 01:22 AM
Sep 19 2022 01:55 AM
@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.
Sep 20 2022 08:46 PM
Please find my dummy version of my data, hope that it can give another input to the solution
Sep 20 2022 08:57 PM - edited Sep 20 2022 08:57 PM
@Ryan_Izzan Having trouble downloading this file. Please make it smaller. A few hundred rows will do.
Sep 20 2022 08:59 PM
Here is the smaller one
Sep 20 2022 09:12 PM
@Ryan_Izzan Will get back to you later and see if I can get it to work on this larger scale.
Sep 20 2022 10:39 PM
@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.
Sep 21 2022 01:07 AM
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,
Sep 21 2022 01:12 AM
@Ryan_Izzan Okay! No problem. I'll see what I can do.
Sep 21 2022 02:22 AM
@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.
Sep 23 2022 12:28 AM
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,
Sep 23 2022 01:28 AM
@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.