Macro to find all combinations that are shared within a list

[mnty89]

I can't currently figure out how to do what I want..


Here is the issue. I have a data set with quite a few lines. There are essentially two aspects to the data. One is the location, the second is the item. Each location has a row for however many items it carries. So if location 1 carries three items, in the dataset it will have location 1, item 1 as an observation, location 1 item 2 as an observation etc..


What I want to figure out are the combinations where a select group of items are all carried at a certain selection of locations. Say I have 1000 locations and 100 items, I want to find the most amount of items I can that are all carried in as many locations as possible. If the solution is for example a group of 20 items that are all carried in 50 locations, that list would be the outcome.


Each location carries some items, but not all. What I want to determine is various mixtures of items that the most amount of locations ALL carry.


The solutions will ideally be churned out in a way that the solutions with the most amount of stores are populated first.


I want to find a way to have Excel loop through somehow and provide an output where it shows a mix of locations that can have the most shared items. The data only has two columns, A a list of locations, and B a list of the items. Each location will repeat the number of times it relates to numerous items. Maybe a way to have it only look for solutions with more than X number of stores, or X number of items as well inputted by the user if possible?


Any ideas?

 

[mnty89]

Input


| Location | Item |
1000|1
1000|2
1000|3
1000|4
1000|5
1005|1
1005|2
1005|3
1005|4
1005|5
1010|1
1010|2
1010|3
1015|1
1015|2
1015|3
1020|1
1020|2
1020|3
1030|6
1030|7




Output




| ShareLocation | ShareItem |
1000|1
1005|2
1010|3
1015|
1020|

 

[mnty89]

I know I didn’t do the best job describing the problem. To summarize in the most simple and easy to explain way(I also uploaded a sample data set and a solution based on the sample), there are ~6000 unique locations/stores, and ~500 unique items that each store may or may not hold. The structure of the data is a line for each store item combination that exists. Not every item is held at each store, so the solutions are the subsets of items that are ALL held in a subset of stores. So in the solution each of those stores will hold every one of the items in the list. So the solution is a unique listing of stores and items that comprise a universal mix where each item is held at that store, and each store already holds that item. I want to receive numerous outputs that are feasible solutions.
Ideally I want to be able to set a variable where I can for example say, exclude solutions for example with less than 50 items or more than 300, or less than 50 stores but less than 2000 , to reduce the overall amount of unneeded solutions. I hope this makes sense now.
Here is the link to the set of sample data csv: http://www83.zippyshare.com/v/3tgC170k/file.html
Here is a link to the solution csv: http://www83.zippyshare.com/v/DUIEHopG/file.html