generate numbers with macros

[vbax]

Hi,

I have these numbers 1,5,10,15,20,25,30,35,40

I have to generate 3 numbers in a row like this

1,5,10
1,10,15
1,15,20
1,25,20 and so on..........

to explain the above series(1,5,10,15,20,25,30,35,40) has 10 different numbers, I have to form many row with just 3 numbers in one row. Like that need all the combination of numbers with above 10 number with 3 numbers in a row.

So there could be many rows if we generate the numbers, can some one help to write a excel macro to generate these numbers

thanks
vbax

[Crooza]

Firstly (1,5,10,15,20,25,30,35,40) is 9 numbers not 10. Is 45 to be included?


If I understand correctly you want every combination of 3 numbers from these 10. That's 10 x 9 x 8 = 720 combinations assuming there's no repeats. Is this correct?

I assume the combination 1,5,10 is the same as 5,10,1 and 1,10,5 etc. You only want this displayed once I assume?

[vbax]

Firstly (1,5,10,15,20,25,30,35,40) is 9 numbers not 10. Is 45 to be included?

Yes include 10 also.

If I understand correctly you want every combination of 3 numbers from these 10. That's 10 x 9 x 8 = 720 combinations assuming there's no repeats. Is this correct?
Why did you say its 10*9*8 ?
Yes and No for Repeats, I can have with repeats and without repeats.

I assume the combination 1,5,10 is the same as 5,10,1 and 1,10,5 etc. You only want this displayed once I assume?

Again yes and No for combinations, I can have with combinations and without combinations.

[mehmetcik]

The Macro for this is quite simple.

I created a User Defined Function instead.

I will post both.

First the UDF.

Paste the UDF into a macro module

Then Type your numbers into a cell eg A8

then type this formula into Say C12

=Combinations(A$8) then fill down and up

Please Login or Register  to view this content.

This Macro does the same thing but the Number List must be in A1 and the output is in column B

Please Login or Register  to view this content.