Possible for Excel?

[Supurbub]

Hello-

I am fairly strong on Excel formulas and have very lightly dabbled with Power Query/Pivot. My work has been making a manual schedule for quite a while and I am trying to get it automated. Our ERP does not have the capability so my best bet is to try to get this to work in Excel... unless there is another suggestion?
To oversimplify- we have giant sheets of material (Umbrellas) that we cut parts out of. These parts go to different companies. Some umbrellas may make 10 of the same part or may make 7 different parts or any combination of.
Our demand for parts fluctuates by month based on customer needs. The goal is to figure out the most efficient way (least # of umbrellas used) to satisfy customer demand.

The image below is contains some made up umbrella and part #s. We have over 75 parts and 20 different part numbers but I would imagine the solution would be the same conceptually.

My demand would look something like this:

The goal is to populate the ? with the minimum amount of umbrellas needed to satisfy the monthly quota. Leftovers can be used the next month (in other words, if you build 8 of 234-4 in May, you can use the last one to support the 1 demand in June)
I wouldn't be opposed to using PowerQuery, formulas, etc. but my work does disable Macros. My ability with them is very weak so I may need extra help if that is the best route (Sorry and thank you in advance!)

Thank you for any help that you can give!

 

[alansidman]

Cannot manipulate data in a picture. Please reload the samples using XL2BB so that we don't have to try and recreate your data.

 

[Supurbub]

Hello-

I am fairly strong on Excel formulas and have very lightly dabbled with Power Query/Pivot. My work has been making a manual schedule for quite a while and I am trying to get it automated. Our ERP does not have the capability so my best bet is to try to get this to work in Excel... unless there is another suggestion?
To oversimplify- we have giant sheets of material (Umbrellas) that we cut parts out of. These parts go to different companies. Some umbrellas may make 10 of the same part or may make 7 different parts or any combination of.
Our demand for parts fluctuates by month based on customer needs. The goal is to figure out the most efficient way (least # of umbrellas used) to satisfy customer demand.

The image below is contains some made up umbrella and part #s. We have over 75 parts and 20 different part numbers but I would imagine the solution would be the same conceptually.
View attachment 113778

My demand would look something like this:
View attachment 113779

The goal is to populate the ? with the minimum amount of umbrellas needed to satisfy the monthly quota. Leftovers can be used the next month (in other words, if you build 8 of 234-4 in May, you can use the last one to support the 1 demand in June)
I wouldn't be opposed to using PowerQuery, formulas, etc. but my work does disable Macros. My ability with them is very weak so I may need extra help if that is the best route (Sorry and thank you in advance!)

Thank you for any help that you can give

Figured out how to get the xl2bb to work. The data is below:

Umbrella Demand.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Umbrella	Creates:	Quantity			# Required by Month	May	June	July	August	September	October	November	December
2	ABC-1	234-1	1			234-1	5	11	2	7	3	6	10	7
3		234-2	3			234-2	3	1	6	3	5	5	2	5
4		234-4	1			234-4	7	1	4	6	9	4	2	3
5	ABC-2	555-1	3			555-1	9	0	5	3	7	3	4	1
6		234-1	2			446-3	2	6	4	5	9	7	4	2
7		446-3	2			777-8	3	4	4	7	3	6	5	7
8	ABC-3	555-1	7
9	ABC-4	234-4	5
10		446-3	1			Umbrella Demand	May	June	July	August	September	October	November	December
11	ABC-5	777-8	9			ABC-1	?
12	ABC-6	777-8	1			ABC-2	?
13		555-1	2			ABC-3	?
14		234-2	1			ABC-4	?
15		234-1	1			ABC-5	?
16		446-3	1			ABC-6	?
Umbrella Part #s

Thank you for the reply and any future help.

 

[alansidman]

Load first table

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Filled Down" = Table.FillDown(Source,{"Umbrella"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Filled Down",{{"Umbrella", type text}, {"Creates:", type text}, {"Quantity", Int64.Type}})
in
    #"Changed Type"

Load Second Table

let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"# Required by Month", type text}, {"May", Int64.Type}, {"June", Int64.Type}, {"July", Int64.Type}, {"August", Int64.Type}, {"September", Int64.Type}, {"October", Int64.Type}, {"November", Int64.Type}, {"December", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"# Required by Month"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"

Join the two tables.

let
    Source = Table.NestedJoin(Table3, {"Creates:"}, Table4, {"# Required by Month"}, "Table4", JoinKind.LeftOuter),
    #"Expanded Table4" = Table.ExpandTableColumn(Source, "Table4", {"Attribute", "Value"}, {"Attribute", "Value"}),
    #"Inserted Multiplication" = Table.AddColumn(#"Expanded Table4", "Multiplication", each [Quantity] * [Value], Int64.Type),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted Multiplication",{{"Multiplication", "Requirements"}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns",{"Umbrella", "Attribute", "Requirements"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Other Columns", List.Distinct(#"Removed Other Columns"[Attribute]), "Attribute", "Requirements", List.Sum)
in
    #"Pivoted Column"

My results

Book2
	F	G	H	I	J	K	L	M	N
18	Umbrella	May	June	July	August	September	October	November	December
19	ABC-1	21	15	24	22	27	25	18	25
20	ABC-2	41	34	27	33	45	35	40	21
21	ABC-3	63	0	35	21	49	21	28	7
22	ABC-4	37	11	24	35	54	27	14	17
23	ABC-5	27	36	36	63	27	54	45	63
24	ABC-6	31	22	26	28	34	30	29	23
Sheet2

Since you did not provide expected results, I hope my solution meets your needs.

 

[Supurbub]

Load first table

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Filled Down" = Table.FillDown(Source,{"Umbrella"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Filled Down",{{"Umbrella", type text}, {"Creates:", type text}, {"Quantity", Int64.Type}})
in
    #"Changed Type"

Load Second Table

let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"# Required by Month", type text}, {"May", Int64.Type}, {"June", Int64.Type}, {"July", Int64.Type}, {"August", Int64.Type}, {"September", Int64.Type}, {"October", Int64.Type}, {"November", Int64.Type}, {"December", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"# Required by Month"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"

Join the two tables.

let
    Source = Table.NestedJoin(Table3, {"Creates:"}, Table4, {"# Required by Month"}, "Table4", JoinKind.LeftOuter),
    #"Expanded Table4" = Table.ExpandTableColumn(Source, "Table4", {"Attribute", "Value"}, {"Attribute", "Value"}),
    #"Inserted Multiplication" = Table.AddColumn(#"Expanded Table4", "Multiplication", each [Quantity] * [Value], Int64.Type),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted Multiplication",{{"Multiplication", "Requirements"}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns",{"Umbrella", "Attribute", "Requirements"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Other Columns", List.Distinct(#"Removed Other Columns"[Attribute]), "Attribute", "Requirements", List.Sum)
in
    #"Pivoted Column"

My results

Book2
	F	G	H	I	J	K	L	M	N
18	Umbrella	May	June	July	August	September	October	November	December
19	ABC-1	21	15	24	22	27	25	18	25
20	ABC-2	41	34	27	33	45	35	40	21
21	ABC-3	63	0	35	21	49	21	28	7
22	ABC-4	37	11	24	35	54	27	14	17
23	ABC-5	27	36	36	63	27	54	45	63
24	ABC-6	31	22	26	28	34	30	29	23
Sheet2

Since you did not provide expected results, I hope my solution meets your needs.

Not quite but thank you for trying.
The numbers should be quite smaller. For example, if I built 21 of ABC-1, I would create
21 of 234-1
63 of 234-2
21 of 234-4

I only need
5 of 234-1
3 of 234-2
7 of 234-4
respectively for May.

Let's focus on just 234-1 for May only. I could build:
5 ABC-1
or 5 ABC-6
or 2 ABC-2 with 1 ABC-1 or ABC-5
That would satisfy the 234-1 quantity for May while also generating a surplus of other pieces. There is some combination of umbrellas that will satisfy the months demand most efficiently.

Another way to say it would be I will need 29 total pieces in May. To build this, there will be a number of umbrellas less than 29 to meet the demand since each umbrellas creates a minimum of 5 pieces.

 

[felixstraube]

We have over 75 parts and 20 different part numbers but

When you wrote "75 parts", did you mean Umbrellas? Which can produce 20 different parts?

 

[felixstraube]

How do you measures efficiency?
Parts needed/Parts produced? The higher the better.
Or min number of umbrellas cut? The lower the better.

 

[Supurbub]

How do you measures efficiency?
Parts needed/Parts produced? The higher the better.
Or min number of umbrellas cut? The lower the better.

Hello. Minimum numbers of umbrellas cut is the most efficient. The material for the umbrellas is extremely expensive.
The quantity of parts does not matter so long as it meets the minimum required each month (including carryover from previous month).

 

[Supurbub]

When you wrote "75 parts", did you mean Umbrellas? Which can produce 20 different parts?

Oops. Good catch. I meant we have 20 different umbrellas that produce different combinations of 75 part numbers.

 

[felixstraube]

Hi. It's still a work in progress, but I want to share how it goes so far. Later it could be done more efficiently with VBA I think.

I rearranged the quantity per umbrella parts table from this:

Umbrella	Creates:	Quantity
ABC-1	234-1	1
ABC-1	234-2	3
ABC-1	234-4	1
ABC-2	555-1	3
ABC-2	234-1	2
ABC-2	446-3	2
ABC-3	555-1	7
ABC-4	234-4	5
ABC-4	446-3	1
ABC-5	777-8	9
ABC-6	777-8	1
ABC-6	555-1	2
ABC-6	234-2	1
ABC-6	234-1	1
ABC-6	446-3	1

To this:

	234-1	234-2	234-4	446-3	555-1	777-8
ABC-1	1	3	1	-	-	-
ABC-2	2	-	-	2	3	-
ABC-3	-	-	-	-	7	-
ABC-4	-	-	5	1	-	-
ABC-5	-	-	-	-	-	9
ABC-6	1	1	-	1	2	1

And tried different values to satisfy the needs of May manually:

Umbrella.xlsx
	B	C	D	E	F	G	H	I	J	K
1		234-1	234-2	234-4	446-3	555-1	777-8	Qty of each umbrella
2	ABC-1	1	3	1	-	-	-	-
3	ABC-2	2	-	-	2	3	-	2
4	ABC-3	-	-	-	-	7	-	-
5	ABC-4	-	-	5	1	-	-	2
6	ABC-5	-	-	-	-	-	9	-
7	ABC-6	1	1	-	1	2	1	3
8	Result	7	3	10	9	12	3	7	44	total parts produced
9
10	# required in May	5	3	7	9	2	3		29	Total parts needed
11									0.659091	Ratio
Sheet1

Cell Formulas
Range	Formula
C1:H1	C1	=TRANSPOSE(SORT(UNIQUE(Table1[Creates:])))
B2:B7	B2	=UNIQUE(Table1[Umbrella])
C2:H7	C2	=IFERROR(FILTER(Table1[[Quantity]:[Quantity]],(Table1[[Umbrella]:[Umbrella]]=$B2)*(Table1[[Creates:]:[Creates:]]=C$1)),0)
C8:H8	C8	=SUM(C2:C7*$I$2:$I$7)
I8	I8	=SUM(I2:I7)
J8,J10	J8	=SUM(C8:H8)
J11	J11	=J10/J8
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C8:H8	Expression	=C8>=C10	text	NO

Now I explain the process used in the later formula to automate the selection of umbrellas. Bear with me, it hopefully will make sense in the end.

I take the UmbrellaData and sort it randomly adding a random number column.

0.097034	ABC-2	2	0	0	2	3	0
0.296713	ABC-6	1	1	0	1	2	1
0.447708	ABC-1	1	3	1	0	0	0
0.681586	ABC-5	0	0	0	0	0	9
0.941866	ABC-4	0	0	5	1	0	0
0.979925	ABC-3	0	0	0	0	7	0

And then i take the needed part list of May (row AI1:AN1), and with the first row of UmbrellaData (Z2:AE2) and calculate how many umbrellas do I need to satisfy the needed parts (for the parts that the given umbrella has) with the formula in AG2.
Then with this max needed umbrella number I generate a random number between 0 and this max number.
After that i take that random number multiply it by the first row of UmbrellaData and substract it from the needed parts row (AI1:AN1) an place the result in AI2:AN2.
So i substract random number of umbrellas parts (in a random order) to the needed parts.

Umbrella.xlsx
	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN
1												5	3	7	9	2	3
2	0.097034	ABC-2	2	0	0	2	3	0		5	4	0	3	7	1	0	3
3	0.296713	ABC-6	1	1	0	1	2	1		3	3	0	0	7	0	0	0
4	0.447708	ABC-1	1	3	1	0	0	0		7	1	0	0	6	0	0	0
5	0.681586	ABC-5	0	0	0	0	0	9		0	0	0	0	6	0	0	0
6	0.941866	ABC-4	0	0	5	1	0	0		2	1	0	0	1	0	0	0
7	0.979925	ABC-3	0	0	0	0	7	0		0	0	0	0	1	0	0	0
Sheet1

Cell Formulas
Range	Formula
AI1:AN1	AI1	=C10:H10
X2:AE7	X2	=LET(d, $B$2:$H$7, req, $C$10:$H$10, rsd, SORT(HSTACK(RANDARRAY(ROWS(d)), d), 1), rsd )
AG2:AG7	AG2	=MAX(IFERROR(ROUNDUP(AI1#/Z2:AE2,0),0))
AH2:AH7	AH2	=RANDBETWEEN(0,AG2)
AI2:AN7	AI2	=LET(n, AI1#-Z2:AE2*AH2, IF(n<0, 0,n))
Dynamic array formulas.

For this to be a usefull result the last row has to be all 0. So that all the monthly needs are satisfied by the umbrellas used.
Then i take the amount of umbrellas used list (AH2:AH7) and with the umbrella list (Y2:Y7) I sort it back to the original order and place it it columns U and V.

Umbrella.xlsx
	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN
1	Sorted														5	3	7	9	2	3
2	ABC-1	7		0.047535	ABC-5	0	0	0	0	0	9		1	0	5	3	7	9	2	3
3	ABC-2	0		0.185815	ABC-3	0	0	0	0	7	0		1	1	5	3	7	9	0	3
4	ABC-3	1		0.30289	ABC-1	1	3	1	0	0	0		7	7	0	0	0	9	0	3
5	ABC-4	2		0.678895	ABC-6	1	1	0	1	2	1		9	7	0	0	0	2	0	0
6	ABC-5	0		0.867311	ABC-2	2	0	0	2	3	0		1	0	0	0	0	2	0	0
7	ABC-6	7		0.922718	ABC-4	0	0	5	1	0	0		2	2	0	0	0	0	0	0
8		17
9
10		0
Sheet1

Cell Formulas
Range	Formula
AI1:AN1	AI1	=C10:H10
U2:V7	U2	=SORT(HSTACK(Y2:Y7,AH2:AH7),1)
X2:AE7	X2	=LET(d, $B$2:$H$7, req, $C$10:$H$10, rsd, SORT(HSTACK(RANDARRAY(ROWS(d)), d), 1), rsd )
AG2:AG7	AG2	=MAX(IFERROR(ROUNDUP(AI1#/Z2:AE2,0),0))
AH2:AH7	AH2	=RANDBETWEEN(0,AG2)
AI2:AN7	AI2	=LET(n, AI1#-Z2:AE2*AH2, IF(n<0, 0,n))
V8	V8	=SUM(V2:V7)
V10	V10	=SUM(AI7#)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
V10	Cell Value	=0	text	NO

Every time I press F9 y get a new random result:

Sorted														5	3	7	9	2	3
ABC-1	1		0.020724	ABC-5	0	0	0	0	0	9		1	0	5	3	7	9	2	3
ABC-2	0		0.087207	ABC-1	1	3	1	0	0	0		7	1	4	0	6	9	2	3
ABC-3	0		0.445034	ABC-6	1	1	0	1	2	1		9	1	3	0	6	8	0	2
ABC-4	6		0.501587	ABC-4	0	0	5	1	0	0		8	6	3	0	0	2	0	2
ABC-5	0		0.573893	ABC-2	2	0	0	2	3	0		2	0	3	0	0	2	0	2
ABC-6	1		0.903689	ABC-3	0	0	0	0	7	0		0	0	3	0	0	2	0	2
	8
	7

Sorted														5	3	7	9	2	3
ABC-1	0		0.1047	ABC-5	0	0	0	0	0	9		1	0	5	3	7	9	2	3
ABC-2	2		0.34529	ABC-2	2	0	0	2	3	0		5	2	1	3	7	5	0	3
ABC-3	0		0.533193	ABC-3	0	0	0	0	7	0		0	0	1	3	7	5	0	3
ABC-4	2		0.539549	ABC-1	1	3	1	0	0	0		7	0	1	3	7	5	0	3
ABC-5	0		0.679267	ABC-6	1	1	0	1	2	1		5	5	0	0	7	0	0	0
ABC-6	5		0.690539	ABC-4	0	0	5	1	0	0		2	2	0	0	0	0	0	0
	9
	0

After many tries I got my best result so far for May, 7 umbrellas:

Umbrella.xlsx
	B	C	D	E	F	G	H	I	J	K
1		234-1	234-2	234-4	446-3	555-1	777-8	Qty of each umbrella
2	ABC-1	1	3	1	-	-	-	-
3	ABC-2	2	-	-	2	3	-	2
4	ABC-3	-	-	-	-	7	-	-
5	ABC-4	-	-	5	1	-	-	2
6	ABC-5	-	-	-	-	-	9	-
7	ABC-6	1	1	-	1	2	1	3
8	Result	7	3	10	9	12	3	7	44	total parts produced
9
10	# required in May	5	3	7	9	2	3		29	Total parts needed
11									0.659091	Ratio
Sheet1

Cell Formulas
Range	Formula
C1:H1	C1	=TRANSPOSE(SORT(UNIQUE(Table1[Creates:])))
B2:B7	B2	=UNIQUE(Table1[Umbrella])
C2:H7	C2	=IFERROR(FILTER(Table1[[Quantity]:[Quantity]],(Table1[[Umbrella]:[Umbrella]]=$B2)*(Table1[[Creates:]:[Creates:]]=C$1)),0)
C8:H8	C8	=SUM(C2:C7*$I$2:$I$7)
I8	I8	=SUM(I2:I7)
J8,J10	J8	=SUM(C8:H8)
J11	J11	=J10/J8
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C8:H8	Expression	=C8>=C10	text	NO

Now I compacted the calculation in one cell and do 1000 of them in one go. And then i select the best result.
Almost every time I get the best result of 7 umbrellas.
Sometimes i get an spill error (don't know why, i had that before when you use a lot of dynamic array formulas. Out of memory or something) but it is only sometimes.

Basically what it does is try random umbrellas quantities and look if the random generated numbers satisfy the monthly needs, then select the best result (least umbrellas cut).
Maybe you wont get the absolute best result every time but i think it will be good enough. And you can process it many time until the numbers are as small as possible.

I hope all this makes some sense.

Here is the working file to download:
Umbrella 2.zip

For you to try it out and let me know what you think.
Could you maybe share the whole umbrella/parts quantity list? And some real data to try how it works with 20 umbrellas and 75 different parts?

If this works we could automate it further with VBA. So you just input the needed parts per month and click a button.