Nov 07 2020 09:34 AM
Hi,
I would like to be assisted with a formula that can calculate as per below;
1. Check the Quantity ordered for a given variety and then divide this amount by the maximum pack rate. to determine the number of boxes to be used.
2. Arrange this information as per the example in column in Column H and I.
Thanks in advance.
Nov 07 2020 10:56 AM
Nov 07 2020 11:05 AM
Thanks Detlef, Would you mind to explain how you worked it out with power query? Not familiar with power query.
Nov 07 2020 11:28 AM
To view the query right click on the green table: Table... -> Edit query.
On the right is a pane with the individual steps. Some have a gear icon which gives more detailed information then the formula bar.
Nov 07 2020 11:45 AM
Nov 07 2020 12:35 PM
To play with M-script
let
Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
addList = Table.AddColumn(
Source,
"Pack rate / box",
each
[
v= [Qty ordered],
n = [Max. Pack rate],
lst = List.Numbers(
[Max. Pack rate],
[Qty ordered]/[Max. Pack rate],
[Max. Pack rate]
),
trs = List.Transform(
lst,
each if _ > v then v-_+n else n
)
][trs],
Int64.Type
),
expandList = Table.ExpandListColumn(
addList,
"Pack rate / box"
),
addIndex = Table.AddIndexColumn(
expandList,
"Box Nr.", 1, 1, Int64.Type
),
keepReportColumns = Table.SelectColumns(
addIndex,
{"Varieties", "Pack rate / box", "Box Nr."}
)
in
keepReportColumns
Nov 08 2020 09:07 AM
Thank you guys !
Though I need to learn power query. Posting this question opened up a new chapter.
Nov 08 2020 09:17 AM
@A_SIRAT , till that time, did you note the formulas based solution i posted? It is not as elegant as Power Query, but could help you for the time being, perhaps!
Nov 08 2020 09:34 AM
The formulae is noted though power query as demonstrated by @Detlef Lewin is really powerful. I just add more varieties and hit the refresh button and the magic happens :)
I have just become more curious by checking videos on Trump excel, excel campus. If any of you has some simpler materials, please share.
Anyway appreciated and I keep both solutions for my records.
Nov 09 2020 12:15 AM
Hi,
I have rephrased the excel question in a different way..." throwing a spanner in the works".
The maximum that can be packed in a box in 1500. After a box is filled up, then the division continues downwards i.e. if the left over is 200 for Jenny for box nr 3, then this will be filled up by 1300 sylvia which is the next variety.
I have attached an example file.
Please assist and thank you for your efforts.