|
Combinations
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 |
Combinations
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 "Boss" wrote: 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 |
Combinations
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" wrote: 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 |
Combinations
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 "Mike H" wrote: 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" wrote: 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 |
Combinations
"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 "Boss" wrote in message ... 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 "Mike H" wrote: 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" wrote: 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 |
Combinations
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 "Boss" wrote: 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 "Mike H" wrote: 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" wrote: 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 |
Combinations
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" wrote: 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 "Mike H" wrote: 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" wrote: 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 |
Combinations
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 "Patrick Molloy" wrote: "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 "Boss" wrote in message ... 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 "Mike H" wrote: 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" wrote: 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 |
Combinations
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. "Sam Wilson" wrote: 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" wrote: 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 "Mike H" wrote: 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" wrote: 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 |
Combinations
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" wrote in message ... 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 "Patrick Molloy" wrote: "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 "Boss" wrote in message ... 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 "Mike H" wrote: 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" wrote: 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 |
Combinations
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" wrote: 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. "Sam Wilson" wrote: 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" wrote: 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 "Mike H" wrote: 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" wrote: 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 |
Combinations
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" wrote: 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. "Sam Wilson" wrote: 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" wrote: 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 "Mike H" wrote: 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" wrote: 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 |
Combinations
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) "Mike H" wrote: 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 "Boss" wrote: 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 "Mike H" wrote: 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" wrote: 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 |
Combinations
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 "Mike H" wrote: 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 "Boss" wrote: 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 "Mike H" wrote: 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" wrote: 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 |
Combinations
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" wrote: 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" wrote: 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. "Sam Wilson" wrote: 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" wrote: 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 "Mike H" wrote: 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" wrote: 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 |
Combinations
This is excellent...
Sam you rock... thansk a lot for your help... Boss "Sam Wilson" wrote: 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" wrote: 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" wrote: 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. "Sam Wilson" wrote: 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" wrote: 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 "Mike H" wrote: 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" wrote: 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 |
Combinations
Excuse for posting here :
Mr . Sam Wilson , to you think this can be done ??? http://groups.google.ro/group/micros...2ca85e5d93cf7# |
| All times are GMT +1. The time now is 06:46 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com