List all combinations

I'm new to Excel and I want to know if there is a function that will list combinations of numbers.

I will simplify what I mean.

Suppose I sell flowers.
Suppose I have 20 different kinds of flowers in my store.
The flowers are Flowers A, B, C, D, etc... until Flower T.

Now, suppose I want to sell my flowers in different combinations of 4 flowers, for example:

Flower A, Flower B, Flower C and Flower D
Flower A, Flower C, Flower D and Flower E
Flower B, Flower C, Flower D and Flower E
Flower P, Flower R, Flower S and Flower T
etc...

Is there an Excel function that could list all the options for me?

Thanks!

Jakob, that was pretty cool! Wow! Thanks! And so quick! You're gifted!

Ok, now I'm trying to understand the code so I can modify it as I make changes.


Here's your code:

Sub Flowers()
fl = "A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T"
flo = Split(fl, ",")
MsgBox LBound(flo) & vbCrLf & UBound(flo)
N = 1
For i = 0 To 19
a = flo(i)
For j = i + 1 To 19
b = flo(j)
For k = j + 1 To 19
c = flo(k)
For l = k + 1 To 19
d = flo(l)
Cells(N, 1) = a
Cells(N, 2) = b
Cells(N, 3) = c
Cells(N, 4) = d
N = N + 1
Next
Next
Next
Next
End Sub

What would I have to do to the code to list 9 flower combinations instead of 4 flower combinations?

Would I have to add the lines below to your code?

e = flo(m)
f = flo(n)
g = flo(o)
h = flo(p)
i = flo(q)

Cells(N, 5) = a
Cells(N, 6) = b
Cells(N, 7) = c
Cells(N, 8) = d

Next
Next
Next

Thanks!

Ok, then, I hate to ask, but, what if I wanted the list displayed from right to left, accross columns, rather than from top to bottom, accross rows?

Go from this result:

1 2 3 4
1 2 3 5
1 2 3 6
1 2 3 7

To this one:

1 1 1 1
2 2 2 2
3 3 3 3
4 5 6 7

Thanks!

Originally Posted by shg

Thare's a workbook that will do this at https://www.box.com/s/b9b9fc06beb63b9562f9

Thanks, shg... I looked at the workbook and though I think it does what I asked for - listing all the combinations - but what I like about Jakobshavn's solution (above) is that I get the results with each each "Item" - or "Flower" in my example- in a particular 4-Item group - in separate cells, which means I can add other attributes to these Items. When I get a result in one cell or line I can't manipualte the data further. Do I make sense?

I love Jakobshavn's solution, I'm trying to see how I can make it scaleable, adding types of "Flowers" and getting larger groups, up to 9 at most. Given a choice, I'd also like the results posted across columns rather than vertically, across rows...

Originally Posted by Jakobshavn

You would need to add more nested For loops. It is easy to do but you will soon run out of rows to store the stuff!

How do I add these nested For loops?

For example, do I just add the line:

Please Login or Register  to view this content.

Or do I also have to add:

Please Login or Register  to view this content.

I'm trying to figure out how to scale this up, 'cause I'd like to scale up to combinations of 5, 6, 7, 8 amd 9 flowers eventually....

Jakob, I'm really impressed with this... How can I decipher the syntax to the language you're using? I'm impressed! Can you explain what each line does?... Thanks!

How do I do text to columns? Export the data? Copy to a text file? I like the output but how do I do it? I feel ignorant....

shg, other than how I do the conversion, I was playing around with the spreadsheet and the results I get are in the inverse order I'd like them. So using A,B,C,D and 4-item groups I get:


D C B A
E C B A
E D B A
E D C A etc...

As oppsoed to:

A B C D
A B C E
A B C F
A B C G

I know I can sort them later, but why does it prsent them that way?...

Originally Posted by martindwilson

data/text to columns
http://www.dailydoseofexcel.com/arch...xt-to-columns/

Thanks, that was helpful!

Originally Posted by shg

Because it puts them in lexical order. ... Notice that those are sorted ascending.

Got it... I understand... Ok, now with the results presented that way, is there an autosort function which would put them in the order I want them - ABCD, ABCE, ABCF... ? ... Thanks, shg, mighty prompt of you!

Originally Posted by shg

... you'd need to sort each row left to right, and then everything top to bottom, if you want them in that order.

Thank you so much, shg!...

Originally Posted by shg

I added a button that reorders the list...

Wow, now that is impressive. It's beautiful! Crazy nice. Downloaded it and am enjoying the hell out of it. Thanks!