List combinations VBA

[BarstoolProphet]

Sry if this might already been covered, but I am new here and was unable to find my solution here. Please help.

Attached is my simple worksheet and a VBA title Aisle working atm. But it crashes due to the results.

In short what I need is a vba written or the existing one edited so that I get what I need in my final results.

In said worksheet you will find 9 aisles that will have items placed in them. Only one item can be selected from each aisle. And it can not be repeated.

So for ex: If apples is selected from aisle 1 ,,it no longer can be selected at any time throughout the process.

Also I found that "duplicates" are occurring,,,which is making the total number of results exceed excel's limits,,and are not needed.

EX: If Milk is selected from Aisle 5 and then Cheese is selected from Aisle 6 ,,my VBA will also include Cheese from Aisle 5 and then select Milk from Aisle 6. This is redundant. I do not need that as an option.

So Milk,Cheese Is the same as Cheese,Milk,,in this example. if i could eliminate those "duplicates" I think that it might stop "crashing"

Thanks in advance for ur help.

Again if we need to write an entirely new vba ,,so be it..

Also I would like the results to be listed on new sheet rather than active sheet.

Thanks

[shg]

EX: If Milk is selected from Aisle 5 and then Cheese is selected from Aisle 6 ,,my VBA will also include Cheese from Aisle 5 and then select Milk from Aisle 6. This is redundant. I do not need that as an option.

If that's true, then can you not eliminate duplicates beforehand?

So in your example,

Row\Col	A	B	C	D	E	F	G	H	I
3	Aisle 1	Aisle 2	Aisle 3	Aisle 4	Aisle 5	Aisle 6	Aisle 7	Aisle 8	Aisle 9
4	Apples	Apples	Spices	Spices	Milk	Milk	Milk	Milk	Milk
5	Oranges	Oranges	Condiments	Condiments	Cheese	Cheese	Cheese	Cheese	Cheese
6	Pears	Pears	Salad Dressings	Salad Dressings	Butter	Butter	Butter	Butter	Butter
7	Peaches	Peaches			Yogurt	Yogurt	Yogurt	Yogurt	Yogurt
8					Cream Cheese	Cream Cheese	Cream Cheese	Cream Cheese	Cream Cheese
9					Buttermilk	Buttermilk	Buttermilk	Buttermilk	Buttermilk
10								Spices	Spices
11								Condiments	Condiments
12								Salad Dressings	Salad Dressings

would reduce to

Row\Col	A	B	C	D	E	F	G	H	I
3	Aisle 1	Aisle 2	Aisle 3	Aisle 4	Aisle 5	Aisle 6	Aisle 7	Aisle 8	Aisle 9
4	Apples		Spices		Milk
5	Oranges		Condiments		Cheese
6	Pears		Salad Dressings		Butter
7	Peaches				Yogurt
8					Cream Cheese
9					Buttermilk
10
11
12

[BarstoolProphet]

I would still need to have the empty aisles filled. So maybe run a subset VBA.

Run all possible combos(no repeats) from 3 different subsets.

Aisle 1 choose 2
Aisle 3 choose 2
Aisle 5 choose 3
Aisle 8 and 9 would be a choose 2 option of everything left over from aisles 3 and 5

EX: If Spices,Condiments,Milk,Cheese,Butter were all selected. The pool to choose from for aisles 8 9 would be the remaining items:

Salad Dressings,Yogurt,Cream Cheese,Buttermilk choose 2

[shg]

198 results; see attachment.

[BarstoolProphet]

thank you so much,,i didnt expect such a quick response and solution.

However something is off. bc the final results should have been 1080 total combos.

For aisles 1 and 2 , there are only 2 items to choose from a pool of 4 options:

Result = 6 unique combos. 3!= 3+2+1

For aisles 3 and 4 , there are only 2 items to choose from a pool of 3 options:

Result = 3 unique combos. 2!= 2+1

For aisles 5,6 and 7 , there are only 3 items to choose from a pool of 6 options:

Result = 10 unique combos.

And the most difficult is the remaining items to fill aisles 8 and 9. Since aisles 3 and 4 should be filled it only leaves 1 item left . And for aisles 5,6,7 there should be 3 items left , for a total of 4 items that should not have been previously used.

For aisles 8 and 9 , there are only 2 items to choose from a pool of 4 remaining options:

Result = 6 unique combos. 3!= 3+2+1

So the final tally is 6x3x10x6 = 1080 total results.

I hope this helps,,and again amazing work you have done. Thank you.

Also the lists in each aisle can change on a monthly basis. I hope I will be able to add to or decrease as needed. EX: the list for aisles 1 and 2 could go from 4 this month to only 3 the next.

[shg]

I list 198 possibilities, you believe there are 1080. Give an example that doesn't appear on the list.

[BarstoolProphet]

Absolutely correct.
I was doing my math wrong,even in the above post, all this time.

I was creating duplicates all the time.

I simply rechecked my math as if to take 7 out of 9 item and found 36 combinations ,,3 of which did not meet criteria.

33x6=198

Done, thank you so much.

However when I add an item to both aisles 3 and 5 . It always freezes on line 940 whne it should have 1980 lines of combinations, well within the limits.

Is this my computer or my version of Excel?

[shg]

Post the workbook.

[BarstoolProphet]

Here ya go.

Again thx for your time.

[shg]

Generates 1806 combinations for me.