ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   List Problem--Please help? (https://www.excelbanter.com/excel-discussion-misc-queries/122344-list-problem-please-help.html)

Frank_Horbelt

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.


Herbert Seidenberg

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