I have a set of data. example
A=1, b=2,c=3, d=4,e=5,f=6,g=7.
I want to divide into 2 groups so that the sum of both groups are as equal as possible
I have a set of data. example
A=1, b=2,c=3, d=4,e=5,f=6,g=7.
I want to divide into 2 groups so that the sum of both groups are as equal as possible
Hi mdsuhair and welcome to the forum,
I love these problems. Here is how I solve them. I get the list of numbers and in the column to the right I create a random number. Then I sort by the random number column which sorts the given numbers in a random order. Then I add the top half and bottom half to see if their sums are equal. If not I do it again. I need to press the recalculate button a few times until the two sums are equal and you have the answer.
If you need to see this in practice, you need to attach a sample file so we can see your real problem and data structure. Please put what is given and what you expect to have as the outcome.
To attach a file, click on "Go Advanced" below the message area and then on the Paper Clip Icon above the message area to attach a sample file.
One test is worth a thousand opinions.
Click the * below to say thanks.
Marvin..thanks for ur response. I have attached a file and perhaps you could try out ur idea and get back to me with ur methodology via an example, again thanks and i do appreciate
Hi,
Find the attached with how I do it. I typed instructions in the workbook. If it doesn't make sense then let me know.
Thanks Marvin. I will give it a try this weekend to see if i understand. thanks so much for ur help,
Try this one:
Thanks Marvin. I found a excel solver method essentially creating 2 buckets which willbe either1 or 0 for each line item and put some constraints to minimize the difference between the buckets, and it works fine; think this approach is more intuitive and will help my team offshore het going quickly
thanks all the same, really appreciate ur help
This solution was tested with 100 random numbers (see attachment). It completed the problem in under 1 second and a single pass of the numbers.
ASSUMPTION:This solution assumes that the resulting 2 groups of numbers must be equal in sum value BUT it does not assume that the groups must have equal number of members. Since the orginal requirements does not indicate groups have to have same number of members.
This solution also assumes it's OK to re-sort the numbers.
Please Login or Register to view this content.
YOUR FEEDBACK: To Say Thanks, or to leave Constructive Comments, please click on the "* Add Reputation" link below.
>Develope Good Habits with MSDN Coding Standards <>How To Add Macros & VBA Code To Your Workbooks<>Best Practices For Referencing Cells, Ranges and Sheets<
Marvin... i tried the excel solver and have run into execution time and accuracy problems. maybe my set up is wrong. can u pls look at my attached file. i am trying to sort the column a ( which is an identifier for the item) . each item in column A contains a certain number of widgets so # 4 has 522 widgets. i want the items arranged in 2 buckets so that the difference between the 2 buckets is a minimum ( the difference between 2 buckets represents wastage). To minimize wastage i am prepared to take a reserve ( 0 or 1, or 2 items in practical terms). What i would like to do is to use ur solver
Last edited by mdsuhair; 05-12-2011 at 01:42 PM. Reason: forgot to add remark
Hi mdsuhair,
I really never used the Excel "Solver" to do any of my calculations. I simply used a random number generator and did a lot of guessing. I believe the Excel Solver needs a formula of some type to attack the problems. My method is much stupider than that. It just guesses and checks the final answer to see if it is better than the best past guess(s). If the answer is better, then it copies the Items, Qty, and buckets to a different spot on the worksheet and keeps trying. Calling my method a solver is really extending what it does. It simply randomly picks buckets and adds the Qty and sees if the answer is better than the best one so far. I have very little logic in the code, other than the final check.
I find that the tool (called computer) can guess a lot of times and a formula might not be needed. Before computers we needed formulas to arrive at answers. I'm kind of going back to pre-logic days and simply trying answers.
I hope this helps you understand the method. Read my past code and modify it a bit to make it work for your new problem.
I've set up a Solver solution in sheet "LDB2290_1 reserve". I changed the setup of the object function and simplified the constraints a bit. I've also changed the Solver option by ticking the box "Asume Non-Negative" and increased the max time to 200 seconds.
Have a look and see if this fits your need.
If speed is your main concern stick to nimrod's solution because Solver realy took time in solving this problem. Perhaps a futher triming of options may increase Solver's speed but in no way it will matchnimrod's result.It completed the problem in under 1 second and a single pass of the numbers.
HTH
Alf
Hi Nimrod,
thanks for the above. Is there a way to do the same, but such that the two groups would:
1. Sum to the same total (or close to it)
2. The groups would be rankes, meaning Group A would be values Min(data) up to X, and Group B would be X+1 up to Max(data).
3. Could this be done for more than one group. i.e. segment three groups as above, each summing to a third of the total sum, or equvalently, each summing to the same number?
Thanks for any help on this!
Hello. I am looking at your excel sheet and it works perfect for my purposes, however, I was wondering what would be involved to increase the number of groups from 2 to say 4 or 6? I am not good with the code, but looking at it and it's assumption of half the data, I would assume it would take lot's of work to make it work with any other number of sets correct?
Hi ehblend, welcome to the forum
2 things...
1. This thread over 2 years old, I doubt anyone is still following it.
2. Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.
If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.
Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks