Combination

[bluetaurean]

I have 3 lists of products which are produced on a line


Line1 Line2 Line3

Apple Mango Mango

Mango Orange Orange

Orange Guava Pineapple

Pineapple Guava

Guava


the total number of combinations for the three lines are 5 x 3 x 4 = 60 i.e eg

Apple Mango Mango;Apple Mango Orange etc...


Is there a formula or code which can create all such combinations. i.e i need a list of all the combinations.

 

[bobhc]

Good day bluetaurean I hope I have read your post correctly, please find a workbook with my interpretation


https://dl.dropbox.com/u/75495784/bluetaurean.xlsx

 

[bluetaurean]

HI BOB,


thanks for responding. I am looking for a formula or code that will actually generate that list of 60 combinations.

 

[bobhc]

the formulas are there, just highlight one of the sub totals to see in formula bar and then on the combination total to see formula in formula bar

 

[Debraj]

Hi bluetaurean,


Can I ask, in which real life case, you want to use this.. as this type of coding are really great for C or C++ exams.. but not in any real case..

I really eager to know the situation..


Please use the below Code in VBA..

[pre]

Sub Combination_bluetaurean()
Dim counter As Long
counter = 1
For i = 1 To Range("a65536").End(xlUp).Row
For j = 1 To Range("b65536").End(xlUp).Row
For k = 1 To Range("c65536").End(xlUp).Row
Range("e" & counter) = Range("a" & i) & " " & Range("B" & j) & " " & Range("c" & k)
counter = counter + 1
Next k
Next j
Next i

MsgBox "Total Combination = " & counter - 1
End Sub

[/pre]

 

[bluetaurean]

HI Debraj,


The real life situation - we will juices, 10 different flavours - orange, peach etc, on 11 different production lines - cans, bottles, pet, etc. there is a restriction on the combination of different flavours than can run simultaneously. i.e we cannot run a mango flavour on more than 5 produciton lines simultabeously and other similary restrictions.

i am therefore trying to derive a list of all such combinations, which i then further filter based on the difficulty level of individual flavour.


the code works perfectly. i will now adapt it to run for 11 production lines.

thanks

 

[xinzhihuxi]

Can you imagine the replica rolex impression you will make on your friends and colleagues if you are wearing Cartier replica watches in the morning and Piaget replica watches in the eveningBy wearing replica rolex replica you can be assured that you will always be one notch above the rest and that means having a greater chance of securing your boss replica watches uk attention or watching the chick of the party approaching you. With Fake Rolex an increase in salary and the subsequent increase in purchasing power, there are many people who can purchase the original stuff, yet most of them prefer to rolex replicas purchase replica watches.

 

[SirJB7]

Hi, bluetaurean!


If you want to explore a solution without VBA code and only just formulas, give a look at this link:

http://chandoo.org/forums/topic/duplicating-row-based-on-another-list


If you choose this option, do you think you'd be able to handle the update for adding the third list (as it works for two)? If not, please just advise.


Regards!

 

[SirJB7]

Hi, bluetaurean!


I've quickly edited it as follows:

Column A: your first list

Column B: your second list

Column C: your third list

Column D: combination of first and second lists (as in the original example)

Column E: copy of column B

Column F: dummy, so as to reuse column D formulas

Column G: same as D, but taking columns D:E instead of A:B


Here's the link:

https://dl.dropbox.com/u/60558749/Combination%20%28for%20bluetaurean%20at%20chandoo.org%29.xlsx


That's for sheet one.


In sheet two, I'll try to make all in one step, but later, if you can wait for a while.


A question: the order should be by columns as in the original file or by rows as you wrote in your first post? Thank you.


Regards!

 

[SirJB7]

Hi, bluetaurean!


Ready second sheet. Download again the file from same link. Just advise if any issue.


If you're always going to choose same value for D1 (row/column precedence for combination) then you can modify (simplify) formulas in E:G columns and delete column D.


If so and you don't want to use helper columns for list indexes, you can replace formula references in columns H:J by the formulas in columns E:G and delete columns E:G.


If so and you don't want to use helper columns for individual combination values, you can replace formula references in column K by the formulas in columns H:J and delete columns H:J.


You can too hide columns D:J until needed to change anything.


Note: all column references are previous to any deletion made.


Regards!

 

[bluetaurean]

Fantastic. Thanks for your help

Regards

 

[SirJB7]

Hi, bluetaurean!

Glad you solved it. Thanks for your feedback and for your kind words too. Welcome back whenever needed or wanted.

Regards!

 

[bobhc]

Good evening bluetaurean


After reading SirJB7s workbook and your first post again I realise I was a mile of the mark.....But SijJB7s formula make interesting reading

 

[SirJB7]

@b(ut)ob(ut)hc

Hi!

I now find very useful having a generic model for combinations, and if it weren't for bluetaurean topic which make me work again on mercedeswys first solution, I wouldn't have got it.

So, thanks to both bluetaurean for the mix of stimulation and inducement and you for your nice words. I hope you both find it great too.

Regards!