![]() |
List Problem--Please help?
Okay, so I have a list of about sixty items (we'll say they are flavors
of ice cream). Each one is just a single flavor (apple, cherry, cinamon, etc.) Is there a way for me to take this list and have excel create (not calculate) the maximum number of combinations of those individual items, assuming that I don't want more than three flavors total? With the three flavors I mentioned, the output would be something like: apple apple cherry apple cinamon apple cherry cinamon cherry cherry cinamon cinamon Notice there are no duplicates, but it provides all possible variations with up to three flavors. How would I do this? Thanks in advance for your input. |
List Problem--Please help?
Since there are 36050 combinations
=COMBIN(60,3)+COMBIN(60,2)+COMBIN(60,1) = 36050 I will simplify the problem to 3 combinations of 6 letters: =COMBIN(6,3)+COMBIN(6,2)+COMBIN(6,1) = 20 + 15 + 6 = 41 Your names have been reduced to single letters (A,B,C,D,E,F) Reverse Pivot Table is used twice. For more info see http://j-walk.com/ss/excel/usertips/tip068.htm Build a matrix that looks like this: Word1 A B C D E F Word2 A TRUE 1 1 1 1 1 B TRUE TRUE 1 1 1 1 C TRUE TRUE TRUE 1 1 1 D TRUE TRUE TRUE TRUE 1 1 E TRUE TRUE TRUE TRUE TRUE 1 F TRUE TRUE TRUE TRUE TRUE TRUE Name the 2 headers with arbitrary names ( Word1, Word2) Enter this formula into the matrix: =IF(Word1=Word2,TRUE,1) Do reverse Pivot Table and Go To Special Constants Logicals Delete Entire Row You should get these 15 items of COMBIN(6,2) A B A C A D A E A F B C B D B E B F C D C E C F D E D F E F Concatenate the 2 columns and create another matrix as above with this formula: =IF(OR(LEFT(Word3)=Word4,RIGHT(Word3)=Word4),TRU E,1) Do reverse Pivot Table and erase the Logicals as above to get the 20 items of COMBIN(6,3) ABC ABD ABE ABF ACD ACE ACF ADE ADF AEF BCD BCE BCF BDE BDF BEF CDE CDF CEF DEF |
All times are GMT +1. The time now is 09:50 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com