most frequently occuring set

Hi,

I am trying to compare multiple data sets which would range from 5 to 35 data points. I want to create a new set of 42 data points that contains the most of these SETS.

For reference, I am concerned with tooling in a machine. The sets I am comparing are the different parts that would potentially get processed in the machine (each part may require anywhere from 5 to 35 different tools for operations on it.) The machine has a 42 tool capacity, and I want to know which tool setup would be able to accomplish the most jobs without changing tools.

I am not sure if Excel is capable of solving a problem like this one, but any help (even to say it's impossible on Excel) would be appreciated.

Thanks.

Don't see why you couldn't do this in excel, although it might depend on how many different "tools" there are as to whether it would be practical. If you only have say, 50 tools to pick your 42 set from, it is a very different problem to if you have, say, 5000...

I had a quick go and came up with this: Book1.xls
there is a macro in module 1 to find the best set using a brute force method.

Edit: even with a small number of tools you are looking at running overnight with a method like this.

Ron, I think that this would be a good generalization, but I would probably need something with a definite answer giving a specific set that would not have to be changed out for so many iterations. Definitely a good method if we decide to do scheduling a little more broadly.

yudlugar, I feel like that looks like what I'm looking for but I don't quite understand the logic of it. Your "new set" contains almost all zeros but a few columns have 1's, and it looks like the formula for the second table will return the same values as the first, as long is the "new set" is all zeros. Can you explain exactly how your idea would work, and where I could identify the solution?

Thanks both of you for your help.

I put the sets at the top with 1 representing a tool being used and 0 not being used. I then put the "new set" on line 23 again with 1 representing used and 0 not used.

Below this I put a helper table that works as follows:
If the set in the corresponding row of the first row contains a 0 for that tool value, the helper table is 0. If the set at the top and the new set contain 1, the helper table is 0, otherwise the helper table is 1.

At the end of this I then summed each row. If the sum comes to 0, then all the tools in the set are contained in the new set.

I then used countif to get the number of 0 sets, which gives you the number of sets you can make from your new set.

I didn't run the solution for it, to be honest, the one I wrote probably isn't going to be useful. As I mentioned it will take a long time but thinking about it there are going to be loads of iterations (basically 2^60 I think) to look at every possible binary number upto 60 1's.

You probably need some way of stepping through each 42 tool combination, rather than any number of tools, comparing the number of sets generated each time. Or combining with Ron's pivot table to "intelligently" search. However, brute force methods will only work for a small number of total tools.