Items / Warehouses

**ElmerS** · 02-13-2010, 06:04 AM

Hi,

The original WB has more rows/Columns but I'm sure a small sample will be enough.

In the attached WB - a two-dim. Table presenting Items and in which ware house they can be found.

The Original task is to FIND (filter-out) 7 random items in order to find the largest number of warehouses.

If, because of that small example, it will be easier to check and present a solution with only 3 or 5 Items filtered - it will be fine as long as it returns the max. numbers of warehouses.
(It will be for great help if the Items number can be filled into a cell - to which the Formula will refer to).

As you may understand - the Item(s) has no meaning - they could be any combination from the 15 Items - the important task is to find the Max. Numbers of warehouses in which 7 random Items are stored.

In the attached table I could find it by many trials of filtering - but as the table is much bigger - I need a Formula and/or VBA [UDF or Macro].

If the task can not be accomplished with Sheet Function(s) - then using VBA is preferable.

Helper column(s) comes at last if and only there is no other way around.

I hope I made myself clear,

Thanks in advance, Elm

**rwgrietveld** · 02-13-2010, 08:25 AM

here a procedure, Please let me know if this is what you want.

1) Give each column (Items) a random number
2) Take the 7 highest (or lowest) columns and use advanced filter to create an intermediate list (New Sheet)
3) See which item (Countif(range, "="& "x") is the highest

This requires VBA coding.
It this what you want?

**rwgrietveld** · 02-13-2010, 09:31 AM

Forget all I said above.

HTML Code:

Press F9 for a new random

**shg** · 02-13-2010, 12:32 PM

If the question can be restated like this:

Which seven items are stored in the largest number of warehouses?

... then there's nothing random about the choices. The brute-force method is to take every combination of 7 items from among the 15 (6435 combinations), and see which yields the largest warehouse coverage.

The original WB has more rows/Columns

That's what would cause the brute-force approach to run out of steam. If there were, say, 100 items with 15 to be chosen, then there would be 253E+15 combinations to evaluate.

The mapping of items to warehouses composes a bipartite graph (http://en.wikipedia.org/wiki/Bipartite_graph), with items on the left, warehouses on the right, and lines connecting them. I think an analogous problem is,

A company can hire 7 people to fill a multiplicity of roles. Which 7 applicants can fulfill the most roles?

I think you could find an ocean of literature on how to solve the problem, none trivial.

**ElmerS** · 02-13-2010, 12:34 PM

Thanks for your efforts.

I must have been misunderstood.

I'm NOT looking for 7 RANDOM items.

I need 7 Items that after filtering all 7 of them (by their "X") the highest number of Warehouses will be visible in col. "A".

Those 7 Items cannot be random because in that given table there can be only one solution.
[In a very rare occasion there may be 2-3 sets of 7 Items that after filtering will present the same quantity of Warehouses - this can be the case especially in a much bigger Table - in such a case it will be wonderful to get all the 7 combinations, but even one set will be more than appreciated).

I Hope I made myself clear this time.

Elm

**ElmerS** · 02-13-2010, 01:03 PM

Thanks shg,
(Sorry for my bad English)

Yes - that is the question (quote):
"Which seven items are stored in the largest number of warehouses?"

I assumed it is noting trivial - so, could you please refer me to the simplest, non-trivial, solution (with Excel, of course).

Elm