Home

Optimization Problem with Fixed Discreet Variables

%3CLINGO-SUB%20id%3D%22lingo-sub-1196268%22%20slang%3D%22en-US%22%3EOptimization%20Problem%20with%20Fixed%20Discreet%20Variables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1196268%22%20slang%3D%22en-US%22%3E%3CDIV%20class%3D%22paragraphNode%20wrappable%22%3E%3CSPAN%20class%3D%22textBox%22%3E%3CSPAN%20class%3D%22textWrapper%22%3EHello!%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%20class%3D%22paragraphNode%20wrappable%22%3E%3CSPAN%20class%3D%22textBox%22%3E%3CSPAN%20class%3D%22textWrapper%22%3EMy%20problem%20is%20relatively%20simple%2C%20but%20I%20can't%20find%20a%20way%20to%20make%20it.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%20class%3D%22paragraphNode%20wrappable%22%3E%3CSPAN%20class%3D%22textBox%22%3E%3CSPAN%20class%3D%22textWrapper%22%3EI%20have%2014%20parts%20of%20type%20A%20and%2014%20parts%20of%20type%20B%20and%20their%20weights.%20I%20need%20to%20group%20they%20in%207%20sets%20containing%202%20of%20each%20(each%20part%20can%20be%20used%20only%20once).%20%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%20class%3D%22paragraphNode%20wrappable%22%3E%3CSPAN%20class%3D%22textBox%22%3E%3CSPAN%20class%3D%22textWrapper%22%3EI%20want%20to%20group%26nbsp%3B%3C%2FSPAN%3E%3C%2FSPAN%3Ethe%20parts%20so%20all%20sets%20are%20as%20close%20as%20possible%20in%20weight.%20Would%20mean%20targeting%20the%20smallest%20StdDev%20of%20the%20total%20weight%20of%20each%20set%3A%3C%2FDIV%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22paragraphNode%20wrappable%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Annotation%202020-02-26%20161715.jpg%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F173628i71681028DF1C7E36%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Annotation%202020-02-26%20161715.jpg%22%20alt%3D%22Annotation%202020-02-26%20161715.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%20class%3D%22paragraphNode%20wrappable%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22paragraphNode%20wrappable%22%3E%3CSPAN%20class%3D%22textBox%22%3E%3CSPAN%20class%3D%22textWrapper%22%3EAnyone%20can%20help%3F%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1196268%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1196449%22%20slang%3D%22en-US%22%3ERe%3A%20Optimization%20Problem%20with%20Fixed%20Discreet%20Variables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1196449%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F568684%22%20target%3D%22_blank%22%3E%40Telles65%3C%2FA%3E%20See%20attached.%20Next%20time%2C%20please%20upload%20an%20Excel%20file%20rather%20than%20a%20screen-shot.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1196486%22%20slang%3D%22en-US%22%3ERe%3A%20Optimization%20Problem%20with%20Fixed%20Discreet%20Variables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1196486%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20the%20attempt!%3C%2FP%3E%3CP%3EOn%20the%20file%20you've%20sent%20the%20sets%20have%20the%20biggest%20possible%20StdDev%20of%20the%20totals.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20to%20be%20able%20to%20combine%20to%20find%20the%20smallest%20possible%20StdDev.%3C%2FP%3E%3CP%3EI've%20also%20tried%20to%20use%20the%20Solver%2C%20did%20not%20work%20because%20I%20need%20it%20to%20iterate%20using%20fixed%20specific%20values%20of%20the%20table.%3C%2FP%3E%3CP%3EThe%20file%20is%20attached%20this%20time!%3C%2FP%3E%3CP%3ERegards%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1196562%22%20slang%3D%22en-US%22%3ERe%3A%20Optimization%20Problem%20with%20Fixed%20Discreet%20Variables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1196562%22%20slang%3D%22en-US%22%3EIs%20set%20A%20always%20smaller%20than%20set%20B%3F%20If%20so%20your%20second%20table%20is%20likely%20to%20be%20the%20best%20solution.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1203182%22%20slang%3D%22en-US%22%3ERe%3A%20Optimization%20Problem%20with%20Fixed%20Discreet%20Variables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1203182%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3BNo%2C%20not%20always.%3C%2FP%3E%3CP%3EI'm%20still%20stuck%20with%20this...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1204079%22%20slang%3D%22en-US%22%3ERe%3A%20Optimization%20Problem%20with%20Fixed%20Discreet%20Variables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1204079%22%20slang%3D%22en-US%22%3EI%20think%20it%20is%20like%20this%3A%3CBR%20%2F%3EEither%20you%20write%20VBA%20code%20that%20tries%20all%20possible%20combinations%20and%20picks%20the%20best%20(complicated%20to%20do%20and%20due%20to%20the%20many%20possibilities%20probably%20very%20slow)%2C%20or%20you%20accept%20the%20second%20best%20solution%20which%20is%20where%20you%20try%20to%20create%20sets%20where%20you%20try%20to%20create%20pairs%20of%20both%20columns%20which%20are%20both%20as%20close%20to%20the%20column%20average%20as%20you%20can%20get.%20This%20is%20done%20by%20combining%20the%20largest%20with%20the%20smallest%20of%20A%2C%20then%20the%20one-but%20largest%20and%20one-but%20smallest%2C%20and%20etcetera.%20Depending%20on%20the%20precise%20numbers%20another%20strategy%20may%20potentially%20give%20a%20better%20result%2C%20but%20this%20strategy%20will%20likely%20work%20in%20more%20cases.%3C%2FLINGO-BODY%3E
Highlighted
New Contributor
Hello!
My problem is relatively simple, but I can't find a way to make it.
I have 14 parts of type A and 14 parts of type B and their weights. I need to group they in 7 sets containing 2 of each (each part can be used only once).
I want to group the parts so all sets are as close as possible in weight. Would mean targeting the smallest StdDev of the total weight of each set:
 
Annotation 2020-02-26 161715.jpg
 
Anyone can help?
5 Replies
Highlighted

@Telles65 See attached. Next time, please upload an Excel file rather than a screen-shot.

Highlighted

@Jan Karel Pieterse 

 

Thank you for the attempt!

On the file you've sent the sets have the biggest possible StdDev of the totals. 

I would like to be able to combine to find the smallest possible StdDev.

I've also tried to use the Solver, did not work because I need it to iterate using fixed specific values of the table.

The file is attached this time!

Regards,

 

 

Highlighted
Is set A always smaller than set B? If so your second table is likely to be the best solution.
Highlighted

@Jan Karel Pieterse No, not always.

I'm still stuck with this...

Highlighted
I think it is like this:
Either you write VBA code that tries all possible combinations and picks the best (complicated to do and due to the many possibilities probably very slow), or you accept the second best solution which is where you try to create sets where you try to create pairs of both columns which are both as close to the column average as you can get. This is done by combining the largest with the smallest of A, then the one-but largest and one-but smallest, and etcetera. Depending on the precise numbers another strategy may potentially give a better result, but this strategy will likely work in more cases.