Combinations

[Boss]

Hi,

I have about 200 numbers in a column. I need to find all the possible
additions which are possible using those numbers. My final aim is to match
the answers with some other data.

I did a lot of search in google but failed. please help.

Thanks!
Boss

 

[Sam Wilson]

There are 2^200 combinations there, which is 1.6 * 10^60...

whatever you're going to do with the result set is going to cause problems
because of its size. Even if you compare 1000 of your results against a
number a second it's going to take 1.6 * 10^50 seconds, which is millions and
millions of years.

You'd have to limit the number of possible constituents of the sum, say 5
and have 5 loops in your code to work out all combinations of 5 or less
numbers.

Sam

 

[Mike H]

Hi,

This isn't clear, you want all possible conbinations of 200 numbers but how
may do you want in each number set? what do you want them to add up to?

Mike

 

[Boss]

Thanks for the reply...

Actually i need to add five or less numbers which are in column A to any
number in column B.

Column A has 200 numbers And column B has about 50 numbers


Col A Col B
256 490
234
325
214
256
147

In the example 256+234 = 490

Please help.
thanks!
Boss

 

[Patrick Molloy]

"please help" doesn't work - thats what everybody ALWAYS does.

even 5 out of 200 is HUGE ie 304,278,004,800 permutaions

what is it exactly that you're trying to achieve? Please give us everything.
... adding a second column was the first surprise

 

[Mike H]

Your clarification has only clouded the issue and I haven't bothered trying

=permut(200,5)

because instinctively I think it is billions of combinations!!

What are you trying to do?

Mike

 

[Sam Wilson]

This will work but won't be fast. It writes the first solution it finds to
column c next to the target number, and assumes you have no column headers:

Sub demo()

Dim a As Integer, b As Integer, c As Integer, d As Integer, e As Integer, f
As Integer, x As Integer

Do While Not IsEmpty(Range("B1").Offset(a, 0))
x = Range("a1").End(xlDown).Row
For b = 0 To x
For c = 0 To x
For d = 0 To x
For e = 0 To x
For f = 0 To x
If Range("a1").Offset(b, 0) + Range("a1").Offset(c,
0) + Range("a1").Offset(d, 0) + Range("a1").Offset(e, 0) +
Range("a1").Offset(f, 0) = Range("B1").Offset(a, 0) Then
Range("C1").Offset(a, 0).Value =
Range("a1").Offset(b, 0).Address(False, False) & ", " & Range("a1").Offset(c,
0).Address(False, False) & ", " & Range("a1").Offset(d, 0).Address(False,
False) & ", " & Range("a1").Offset(e, 0).Address(False, False) & ", " &
Range("a1").Offset(f, 0).Address(False, False)
GoTo EndLoop
End If
Next f
Next e
Next d
Next c
Next b

EndLoop:
a = a + 1
Loop

End Sub

 

[Boss]

Thanks for your reply...

I have got my head broken over this now....

What i need is...

I have two list of numbers... I need to compare these two list. Col A and
Col B.

Now, if Cell A1 value = any cell value in Col B then i need to shade both
the cells.

But if cell A1 is not equal to any cell in Col B then i need to add any two
numbers in Col B which would be equal to cell A1 value.

If A1 value not matched then i need to add three values from col B and so
on. At max i would add five values of col B to get A1 value.

Same for A2,A3 .....

Hope this make the case clear...
Thanks!
Boss

 

[Sam Wilson]

After your further explanation, I've written this with column B and A
reversed. It's going to add up combinations from A to try to find targets in
B. Change A1 to B1 and B1 to A1 to make it work.

 

[Patrick Molloy]

better

well the first is easy , use countif() in column C....anything with 1 is a
match!
c1 := =COUNTIF(B:B,A1)
replicate down for all 200 in A

then it gets more difficult... you'd need to see what perms you have after
you strip out of B anything of value > than the value in A that you're
checking
If you're down to 5 numbers out of a reduced list of 20, then you'll still
get almost 2 million combinations

 

[Boss]

Thanks a lot for your help...

But i cannot repeat number... In other words one number cannot be used again
for one comparison.

Hope you solve this issue the way you did other...

Thx!
Boss

 

[Boss]

Thanks a lot for your help...

But i cannot repeat number... In other words one number cannot be used again
for one comparison.

Hope you solve this issue the way you did other...

Thx!
Boss

 

[Sam Wilson]

Ok, change

For b = 0 To x
For c = 0 To x
For d = 0 To x
For e = 0 To x
For f = 0 To x

To

For b = 4 To x
For c = 3 To b-1
For d = 2 To c-1
For e = 1 To d-1
For f = 0 To e-1

and insert a 0 at the top of row B (you need a zero to represent unused
numbers in a combination)

 

[Sam Wilson]

Ok, change

For b = 0 To x
For c = 0 To x
For d = 0 To x
For e = 0 To x
For f = 0 To x

To

For b = 4 To x
For c = 3 To b-1
For d = 2 To c-1
For e = 1 To d-1
For f = 0 To e-1

 

[Sam Wilson]

Ok, change

For b = 0 To x
For c = 0 To x
For d = 0 To x
For e = 0 To x
For f = 0 To x

To

For b = 4 To x
For c = 3 To b-1
For d = 2 To c-1
For e = 1 To d-1
For f = 0 To e-1

and insert a 0 at the top of row B (you need a zero to represent unused
numbers in a combination)

 

[Boss]

This is excellent...
Sam you rock... thansk a lot for your help...

Boss

 

[ytayta555]

Excuse for posting here :

Mr . Sam Wilson , to you think this can be done ???
http://groups.google.ro/group/micro...amming/browse_thread/thread/2652ca85e5d93cf7#